【Transact-SQL(T-SQL)】创建SQLServer2019 用户

• https://learn.microsoft.com/zh-cn/sql/t-sql/statements/create-login-transact-sql?view=sql-server-ver16

• 主要副本创建登录名、用户名

创建登录名

CREATE LOGIN TestLogin WITH PASSWORD = 'SuperSecret52&&' DEFAULT_DATABASE=[master],

DEFAULT_LANGUAGE=[简体中文],

CHECK_EXPIRATION=OFF,CHECK_POLICY=OFF;

SELECT name, sid FROM sys.sql_logins WHERE name = 'TestLogin';

GO

创建用户USE AdventureWorks2022;

CREATE USER Wanida FOR LOGIN WanidaBenshoof WITH DEFAULT_SCHEMA = dbo;

GO

用户赋权：

GRANT SELECT ON table_name TO user_name;
GRANT SELECT ON view_name TO user_name;
GRANT EXECUTE ON OBJECT::procedure_name TO user_name;
GRANT SELECT ON SCHEMA::schema_name TO user_name;  

架构 Person 的 SELECT 权限授予数据库用户 WilJo

GRANT SELECT ON SCHEMA :: Person TO WilJo WITH GRANT OPTION;

例子：

GRANT SELECT courses_view TO test_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON books TO test_user;
GRANT SELECT ON SCHEMA::test_schema TO test_user;
GRANT EXECUTE ON OBJECT::CourseSales TO test_user;
DROP USER test_user;
GO

• 列出数据库主体的所有权限

SELECT pr.principal_id, pr.name, pr.type_desc, pr.authentication_type_desc, pe.state_desc, pe.permission_name FROM sys.database_principals AS pr JOIN sys.database_permissions AS pe ON pe.grantee_principal_id = pr.principal_id;

• 辅助副本创建登录名、用户名

CREATE LOGIN TestLogin WITH PASSWORD = 'SuperSecret52&&',

SID = 0x241C11948AEEB749B0D22646DB1A19F2,check_expiration=OFF,check_policy=ON,

default_database = master,default_language=简体中文;

SELECT * FROM sys.sql_logins WHERE name = 'TestLogin';

GO 

• 主要副本-修改用户

1. 修改数据库用户名、默认架构、密码

ALTER USER Philip WITH NAME = Philipe ,

DEFAULT_SCHEMA = Development , PASSWORD = 'W1r77TT98%ab@#' OLD_PASSWORD = 'New Devel0per' ,

DEFAULT_LANGUAGE= French ;

GO

　　2、修改登录名密码

ALTER LOGIN Mary5 WITH PASSWORD = '<enterStrongPasswordHere>';

 

也可以采用下面的T-SQL在主要副本生产创建登录名的SQL，然后在辅助副本执行创建登录名。

SELECT  'CREATE LOGIN [' + p.name + '] '
       + CASE WHEN p.type IN ( 'U', 'G' ) THEN 'FROM windows '
              ELSE ''
         END + 'WITH ' + CASE WHEN p.type = 'S'
                              THEN 'password = '
                                   + master.sys.fn_varbintohexstr(l.password_hash)
                                   + ' hashed, ' + 'sid = '
                                   + master.sys.fn_varbintohexstr(l.sid)
                                   + ', check_expiration = '
                                   + CASE WHEN l.is_expiration_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END + 'check_policy = '
                                   + CASE WHEN l.is_policy_checked > 0
                                          THEN 'ON, '
                                          ELSE 'OFF, '
                                     END
                                   + CASE WHEN l.credential_id > 0
                                          THEN 'credential = ' + c.name
                                               + ', '
                                          ELSE ''
                                     END
                              ELSE ''
                         END + 'default_database = '
       + p.default_database_name
       + CASE WHEN LEN(p.default_language_name) > 0
              THEN ', default_language = ' + p.default_language_name
              ELSE ''
         END
FROM    sys.server_principals p
       LEFT JOIN sys.sql_logins l
       ON p.principal_id = l.principal_id
       LEFT JOIN sys.credentials c
       ON l.credential_id = c.credential_id
WHERE   p.type IN ( 'S', 'U', 'G' )
       AND p.name NOT IN ( 'sa')
               AND p.name NOT LIKE '%##%'
               AND p.name NOT LIKE '%NT SERVICE%'
               AND p.name NOT LIKE '%NT AUTHORITY%'