-
https://learn.microsoft.com/zh-cn/sql/t-sql/statements/create-login-transact-sql?view=sql-server-ver16
- 主要副本创建登录名、用户名
创建登录名CREATE LOGIN TestLogin WITH PASSWORD = 'SuperSecret52&&';
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;
例子：
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;
SELECT * FROM sys.sql_logins WHERE name = 'TestLogin';
GO