【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，然后在辅助副本执行创建登录名。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

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%'