Shulou(Shulou.com)06/01 Report--

A summary of the https://docs.microsoft.com/zh-cn/sql/t-sql/statements/grant-transact-sql?view=sql-server-2017 permissions of official documents

1. The roles at the instance level are fixed, that is, public, sysadmin, securityadmin, serveradmin, setupadmin, processadmin, diskadmin, dbcreator, bulkadmin

2. Each database has different roles. SQLAgentUserRole, SQLAgentReaderRole and SQLAgentOperatorRole are introduced into msdb database, while other databases do not have these roles.

3. The login name belongs to the instance-level CREATE LOGIN.

4. The user name is CREATE USER at the database level.

5. The additional users who install the interface Specify SQL Server administratorss will be displayed in the Security--logins column of SSMS when opening the instance using the SSMS graphical interface.

6. Permissions are written in the library

6.1. when a login is authorized on the secondary copy of AG, it is impossible to authorize the primary copy of the error reporting database read_only,AG. After authorization, permissions are automatically synchronized from the primary copy of AG to the secondary copy of AG.

6.2. two instances An and B have the same login name. A login on A has the owner permission of a database DB1. If the DB1 is restored to B, it is found that the login name on B also automatically has the owner permission of database DB1. After the DB1 on B is deleted, the login name on B does not have the owner permission of database DB1. After restoring DB1 to B, the login name on B automatically has the owner permission of database DB1. Of course, if two instances An and B do not have the same login name, and the login name user1 on A has the owner permission of a database DB1, restore DB1 to B, but there is no login name user1 on B, and the recovery does not report an error. I feel that during the recovery process, a statement ALTER ROLE [db_owner] ADD MEMBER [user1] was actually executed. Although there is no user1 on B, the recovery process is not affected.

7. when the login name contains special characters, when the login name is used in the code, use parentheses in [] instead of "" double quotation marks

8. A login has the permission to execute sp under a database, as long as it has the db_datareader role and execute

9. The permissions owned by grant select to username are not equal to those of the db_datareader role.

10. There is sometimes a difference between manual authorization and SSMS graphical interface. For example, if a login in SSMS graphical interface is checked with the public permission of a database, it means that the user has the permission to connect to the database, which is not equal to use dbname. Grant connect to username, because the login name of the graphical interface selects public in a database, and schema is dbo. After the grant connect to username statement is executed, the SSMS graphical interface can see that schema is username. After that, the public permission of the login name in this database cannot be revoked in the graphical interface. An error the database principal owns a schema in the database,and cannot be dropped will be reported, and only revoke connect from username can be executed to cancel it.

11. There is no operation like grant connect on dbname to username

12. With regard to users or groups in the operating system, if the user is already an administrator group of the OS system, the user directly has read and write access to all DB, because the user belongs to BUILTIN\ Administrators; in the database. If the user belongs to a login group in the database, such as user A1 belongs to the database login group DA\ DBA1, then DA\ DBA1 has permissions, and A1 also has permissions. How to determine whether it is a user or a group in the database login list? look at the icon in front of this object. If it is a small icon for one person, it is a user, and if it is a small icon for two people, it is a group.

Login creation syntax

CREATE LOGIN [Domain\ sqlprocess] FROM WINDOWS WITH DEFAULT_DATABASE= [master], DEFAULT_LANGUAGE= [us _ english]

Query all logins (login is based on the instance level, not at a database level)

Select * from sys.server_principals where type in ('Utopia, Goblin, Gemma, S')

User name creation syntax

Use dbname

CREATE USER [Domain\ sqlprocess] FOR LOGIN [Domain\ sqlprocess] WITH DEFAULT_SCHEMA= [domain\ sqlprocess]

Query all user names under a database (the user name is based on a database)

SELECT * FROM testdb1.sys.sysusers where status0

When the graphical interface adds the login name Domain\ Wei and gives that login db_datareader permission for a database msdb, the following three statements are actually executed in order

USE [master]

GO

CREATE LOGIN [Domain\ Wei] FROM WINDOWS WITH DEFAULT_DATABASE= [master]

GO

USE [msdb]

GO

CREATE USER [Domain\ Wei] FOR LOGIN [Domain\ Wei]

GO

USE [msdb]

GO

ALTER ROLE [db_datareader] ADD MEMBER [Domain\ Wei]

GO looks at which users have which role permissions under certain data

USE DB

WITH CTE AS

(

SELECT u.name AS UserName

G.name AS dbRole

'√'as' flag'

FROM sys.database_principals u

INNER JOIN sys.database_role_members m ON u.principal_id = m.member_principal_id

INNER JOIN sys.database_principals g ON g.principal_id = m.role_principal_id

)

SELECT * FROM CTE PIVOT (MAX (flag) FOR dbRole IN ([public]

[db_owner]

[db_accessadmin]

[db_securityadmin]

[db_ddladmin]

[db_backupoperator]

[db_datareader]

[db_datawriter]

[db_denydatareader]

[db_denydatawriter]) as rg; role

Query the role name at the instance level

Select * from sys.server_principals where type='R'

Query the role names owned by a database

Select * from sys.database_principals where type='R'

Each data has different roles. For example, SQLAgentUserRole, SQLAgentReaderRole and SQLAgentOperatorRole are introduced into msdb databases, while other databases do not have these roles.

SQLAgentReaderRole has SELECT permission to the view msdb.dbo.sysjobs_view (inheriting the permission of SQLAgentUserRole), and the GUI interface obtains data from the view when operating. So after adding to this role, expanding the job will return all jobs.

When we use statements to query jobs, we are used to starting directly with tables such as msdb.dbo.sysjobs. However, the SQLAgentUserRole role does not have SELECT permissions on such tables, so regular statements report denying SELECT permissions on objects.

Query the member information owned by a role under a database

SELECT DP1.name AS DatabaseRoleName

Isnull (DP2.name,'No members') AS DatabaseUserName

FROM sys.database_role_members AS DRM

RIGHT OUTER JOIN sys.database_principals AS DP1

ON DRM.role_principal_id = DP1.principal_id

LEFT OUTER JOIN sys.database_principals AS DP2

ON DRM.member_principal_id = DP2.principal_id

WHERE DP1.type ='R'

ORDER BY DP1.name; a login has the permission to execute sp under a database, as long as it has the db_datareader role and execute.

Use dbname

ALTER ROLE [db_datareader] ADD MEMBER [Domain\ account]

Grant execute to [Domain\ account]; see which permissions are granted to SQLServer users by using grant command operations

Use dbname

Exec sp_helprotect @ username = 'username'

In the DB1 database, the operation assigned to db_datareader for the Domain\ account login

USE [DB1]

GO

IF NOT EXISTS (SELECT 1 FROM cndb.sys.sysusers WHERE [NAME] = 'Domain\ account')

BEGIN

CREATE USER [Domain\ account] FOR LOGIN [Domain\ account]

END

ALTER ROLE [db_datareader] ADD MEMBER [Domain\ account]; create a read-only role db_reader

CREATE SERVER ROLE [db_reader]

GRANT VIEW ANY DATABASE TO [db_reader]

GRANT CONNECT ANY DATABASE TO [db_reader]

GRANT SELECT ALL USER SECURABLES TO [db_reader]

Understanding of why the local administrator cannot log in to sqlserver

Reason: after installing the database, administrator is not added to the login users of sqlserver.

For example, the name of a server is dbprod127, but there is no builtin\ administrator and dbprod127\ administrator in the login name, so after logging in to the operating system using local administrator, SSMS cannot log in to the local sqlserver database to modify permissions and report an error Cannot add the principal,incorrect syntax near 'XX'.

ALTER ROLE [db_datareader] ADD MEMBER [Domain\ wang]

Error report: Cannot add the principal 'Domain\ wang', because it does not exist or you do not have permission

The solution is to modify the code as follows and add user.

IF NOT EXISTS (SELECT TOP (1) 1 FROM sys.database_principals WHERE name = 'Domain\ wang')

BEGIN

CREATE USER [Domain\ wang] FOR LOGIN [Domain\ wang]

END

GO

When the ALTER ROLE [db_datareader] ADD MEMBER [Domain\ wang] login contains special characters, use [] parentheses instead of "" double quotation marks

The code in job will report an error directly as follows: incorrect syntax near 'Domain\ wang'

Grant execute to "Domain\ j.wang"

Just modify it as follows

Role of the grant execute to [Domain\ j.wang] database

Public

The public role is a special database role to which every database user belongs. Public role:

-- captures all default permissions of users in the database.

Users, groups, or roles cannot be assigned to it because they belong to that role by default.

Contained in each database, including master, msdb, tempdb, model, and all user databases.

It can't be removed.

Db_owner

-- activities of all database roles, as well as other maintenance and configuration activities in the database.

-- permissions for this role span all other fixed database roles.

Db_accessadmin

-add or remove Windows NT 4.0 or Windows 2000 groups and users and SQL Server users from the database.

Db_datareader

-- View all data from all user tables in the database.

Db_datawriter

-add, change, or delete data from all user tables in the database

Db_ddladmin

-- add, modify, or remove objects from the database (run all DDL)

Db_securityadmin

-- manage roles and members of SQL Server 2000 database roles, and manage statement and object permissions in the database

Db_backupoperator

-- have the permission to back up the database

Db_denydatareader

-deny permission to select database data

Db_denydatawriter

Deny permission to change database data

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.