【SQL Server】：数据库用户角色及权限

 

添加域账号登录权限到实例下：

USE [master]
GO
CREATE LOGIN [Domain\Account] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

 

添加SQL Server 认证权限到实例下：

USE [master]
GO
CREATE LOGIN [Account] WITH PASSWORD=N'Account', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

 

授予数据库服务器级别角色：

USE [master]
GO
EXEC master..sp_addsrvrolemember @loginame = N'Account Name', @rolename = N'Server Role Name'
GO

授予数据库的访问权限：

Use [Name]
GO
CREATE USER [Account] FOR LOGIN [Account]
GO

为用户配置数据库访问角色：

Use [Name]
GO
EXEC sp_addrolemember N'Database Role Name', N'Account'
GO

授予查看存储过程的权限：

<pre class="sql" name="code">USE [Name]
GO
GRANT VIEW DEFINITION TO [Account]
GO

查询指定DB上所有用户和角色：

USE [Name]
GO

SELECT  member.name AS MemberName ,
        role.name AS RoleName
FROM    sys.database_role_members s
        LEFT JOIN sys.database_principals AS role ON s.role_principal_id = role.principal_id
        LEFT JOIN sys.database_principals AS member ON s.member_principal_id = member.principal_id
GO

 

查询实例下所有用户和角色：

USE [master]GO
SELECT  member.name AS MemberName ,
        role.name AS RoleName
FROM    sys.server_role_members
        JOIN sys.server_principals AS role ON sys.server_role_members.role_principal_id = role.principal_id
        JOIN sys.server_principals AS member ON sys.server_role_members.member_principal_id = member.principal_id
GO