SQL server permission management and SQL server permission management

SQL server permission management and SQL server permission management
1. Set a Logon account to view only some databases

Refer to tn view any database Permissions
Use sp_addlogin to create a login name
-Basic: User Logon account
-Basic123456: User Logon Password

USE masterGOexecute sp_addlogin 'basic','basic123456'

-View any database permission control: displays the sys. databases and sys. sysdatabases views and metadata in the sp_helpdb system stored procedures.
The following statement can be used to query the status:

SELECT l.name as grantee_name, p.state_desc, p.permission_name FROM sys.server_permissions AS p JOIN sys.server_principals AS l ON   p.grantee_principal_id = l.principal_idWHERE permission_name = 'VIEW ANY DATABASE' ;GO

Deny basic login name access to any database (not visible)
Deny view any database to [basic];
-- Grant view any database to <login> GRANT specific logon accounts

To view a database such as BasicData in basic, run the following statement:

ALTER AUTHORIZATION ON DATABASE::BasicData TO [basic]

-Use BasData
-This step cannot be executed, and the system prompts that the basic account already exists.
-After testing, remove the previous steps for creating an account. This statement can be executed normally.
-Create a basic account in the database BasicData
-The first basic is the login name.
-The second basic is the database account.
-Execute sp_grantdbaccess 'basic ', 'Basic'
-Add the TestUser account to the database role as the database owner.
-Execute sp_addrolemember 'db _ owner', 'Basic'

2. Grant and revoke table Permissions

--- Grant the user person to the table Person. address modification permission USE AdventureWorks2008R2; grant update on Person. address TO person; GO --- grant the user person TO the table Person. address INSERT permission USE AdventureWorks2008R2; grant insert on Person. address TO person; GO --- grant the user person TO the table Person. USE AdventureWorks2008R2; grant delete on Person. address TO person; -- Grant the dbo stored procedure TO the user. grant execute on dbo. prc_errorlog TO person

-- REVOKE and modify USE AdventureWorks2008R2; REVOKE update ON Person. address FROM person; USE AdventureWorks2008R2; REVOKE alter ON Person. address FROM person; -- Recycle and delete USE AdventureWorks2008R2; REVOKE delete ON Person. address FROM person; -- REVOKE Query USE AdventureWorks2008R2; REVOKE select ON Person. address FROM person;

3. How to Set column Permissions

I. Method 1: Use a view

Restrict the user to view only a few specific columns, set them to a view, and then perform permission control on The View.

Ii. Method 2: Use the GRANT statement

1. Grant the query permission for the related columns (SELECT)

In database db1, the username UserA can only have the permission to query the BusinessEntityID, NationalIDNumber, and LoginID fields in the Employee table. Other fields cannot be queried.

Grant select (BusinessEntityID, NationalIDNumber, LoginID) ON Employee TO UserA

2. Grant related column modification permission (Update)

For UserB, only the AddressLine1 and AddressLine2 fields in the Address table can be modified. Other fields cannot be modified.

Grant update (AddressLine1, AddressLine2) ON Address TO UserB

4. query permission

Logon table

Select * from master. sys. syslogins

Login Name and server role Association Table

Select * from sys. server_role_members

Server role table

Select * from sys. server_principals

Query the server roles owned by the logon name

Select SrvRole = g. name, MemberName = u. name, MemberSID = u. sid
From sys. server_role_members m inner join sys. server_principals g on g. principal_id = m. role_principal_id inner join sys. server_principals u on u. principal_id = m. member_principal_id

Database User table

Select * from sysusers

Database User table role Association Table
Select * from sysmembers

Database role table
Select * from sys. database_principals

Query roles owned by database users
Select ta. name as username, tc. name as databaserole from sysusers ta inner join sysmembers tb on ta. uid = tb. memberuid inner join sys. database_principals tc on tb. groupuid = tc. principal_id

Query the logon names associated with the current database user

 use AdventureWorks2008R2  select ta.name as loginname,tb.name as databaseusername from master.sys.syslogins ta inner join sysusers tb on ta.sid=tb.sid 

If you restore the current database to another server instance, there will also be a person login user on that server, you will find that the sid of the two is different,
Because the sid is different, the login user does not have the permission to access the current database. We need to associate the two.

Associate the login name with the database user (fl the sid of the database user to the sid of the login name)

   use AdventureWorks2008R2    EXEC sp_change_users_login 'Update_One', 'person', 'person'    Go

5. Supplement

USE [master] GO --- create login [person] with password = n' person ', DEFAULT_DATABASE = [news], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFFGOUSE [news] GO --- CREATE a database user create user [person] for login [person] GOUSE [news] GO under the specified database grant SELECT to the user under the database, DELETE, UPDATE, INSERT, and EXECUTE permissions. Grant select, DELETE, UPDATE, INSERT, execute to person;

Refer to SQL Server permission management
Over.

Top

0

Step on

0

View comments