数据库权限配置例子第一部分

1)建立两个数据库。DB1和DB2

USE master;
GO
CREATE DATABASE DB1;
GO
CREATE DATABASE DB2;
GO

2)建立一个超级管理员，管理两个用户

USE master
CREATE LOGIN Admin WITH PASSWORD='123',
DEFAULT_DATABASE=DB1;
GO
EXEC sp_addsrvrolemember 'Admin','sysadmin';
GO

3）分别建立两个数据库的管理用户，建立的用户除了连接，什么权限也没有，因为他不属于任何的角色

USE master
CREATE LOGIN AdminDB1 WITH PASSWORD='123',
DEFAULT_DATABASE=DB1;
GO
USE DB1;
CREATE USER AdminDB1 FOR LOGIN AdminDB1
GO

USE master
CREATE LOGIN AdminDB2 WITH PASSWORD='123',
DEFAULT_DATABASE=DB2;
GO
USE DB2;
CREATE USER AdminDB2 FOR LOGIN AdminDB2
GO

3)给数据库用户赋予权限，可以单独一个个权限的授予，也可以直接定义它的角色。
(1)tips:获取DATABAS的全部权限;

SELECT permission_name
FROM SYS.fn_builtin_permissions('DATABASE')

结果如下：

CREATE TABLE
CREATE VIEW
CREATE PROCEDURE
CREATE FUNCTION
CREATE RULE
CREATE DEFAULT
BACKUP DATABASE
BACKUP LOG
CREATE DATABASE
CREATE TYPE
CREATE ASSEMBLY
CREATE XML SCHEMA COLLECTION
CREATE SCHEMA
CREATE SYNONYM
CREATE AGGREGATE
CREATE ROLE
CREATE MESSAGE TYPE
CREATE SERVICE
CREATE CONTRACT
CREATE REMOTE SERVICE BINDING
CREATE ROUTE
CREATE QUEUE
CREATE SYMMETRIC KEY
CREATE ASYMMETRIC KEY
CREATE FULLTEXT CATALOG
CREATE CERTIFICATE
CREATE DATABASE DDL EVENT NOTIFICATION
CONNECT
CONNECT REPLICATION
CHECKPOINT
SUBSCRIBE QUERY NOTIFICATIONS
AUTHENTICATE
SHOWPLAN
ALTER ANY USER
ALTER ANY ROLE
ALTER ANY APPLICATION ROLE
ALTER ANY SCHEMA
ALTER ANY ASSEMBLY
ALTER ANY DATASPACE
ALTER ANY MESSAGE TYPE
ALTER ANY CONTRACT
ALTER ANY SERVICE
ALTER ANY REMOTE SERVICE BINDING
ALTER ANY ROUTE
ALTER ANY FULLTEXT CATALOG
ALTER ANY SYMMETRIC KEY
ALTER ANY ASYMMETRIC KEY
ALTER ANY CERTIFICATE
SELECT
INSERT
UPDATE
DELETE
REFERENCES
EXECUTE
ALTER ANY DATABASE DDL TRIGGER
ALTER ANY DATABASE EVENT NOTIFICATION
ALTER ANY DATABASE AUDIT
VIEW DATABASE STATE
VIEW DEFINITION
TAKE OWNERSHIP
ALTER
CONTROL

给AdminDB1赋予全部的权限。

例如：

USE DB2
GO
GRANT
CREATE TABLE,
CREATE VIEW,
CREATE PROCEDURE,
CREATE FUNCTION,
CREATE RULE,
CREATE DEFAULT,
BACKUP DATABASE,
BACKUP LOG,
CREATE TYPE,
CREATE ASSEMBLY,
CREATE XML SCHEMA COLLECTION,
CREATE SCHEMA,
CREATE SYNONYM,
CREATE AGGREGATE,
CREATE ROLE,
CREATE MESSAGE TYPE,
CREATE SERVICE,
CREATE CONTRACT,
CREATE REMOTE SERVICE BINDING,
CREATE ROUTE,
CREATE QUEUE,
CREATE SYMMETRIC KEY,
CREATE ASYMMETRIC KEY,
CREATE FULLTEXT CATALOG,
CREATE CERTIFICATE,
CREATE DATABASE DDL EVENT NOTIFICATION,
CONNECT,
CONNECT REPLICATION,
CHECKPOINT,
SUBSCRIBE QUERY NOTIFICATIONS,
AUTHENTICATE,
SHOWPLAN,
ALTER ANY USER,
ALTER ANY ROLE,
ALTER ANY APPLICATION ROLE,
ALTER ANY SCHEMA,
ALTER ANY ASSEMBLY,
ALTER ANY DATASPACE,
ALTER ANY MESSAGE TYPE,
ALTER ANY CONTRACT,
ALTER ANY SERVICE,
ALTER ANY REMOTE SERVICE BINDING,
ALTER ANY ROUTE,
ALTER ANY FULLTEXT CATALOG,
ALTER ANY SYMMETRIC KEY,
ALTER ANY ASYMMETRIC KEY,
ALTER ANY CERTIFICATE,
SELECT,
INSERT,
UPDATE,
DELETE,
REFERENCES,
EXECUTE,
ALTER ANY DATABASE DDL TRIGGER,
ALTER ANY DATABASE EVENT NOTIFICATION,
ALTER ANY DATABASE AUDIT,
VIEW DATABASE STATE,
VIEW DEFINITION,
TAKE OWNERSHIP,
ALTER,
CONTROL
TO AdminDB2;

给AdminDB1 赋予权限只要修改AdminDB2就可以了。