SQL Server you will need to maintain user, groups and roles to access certain data.
Predefined database roles
db_owner: Members have full access.
db_accessadmin: Members can manage Windows groups and SQL Server logins.db_datareader: Members can read all data.
db_datawriter: Members can add, delete, or modify data in the tables.
db_ddladmin: Members can run dynamic-link library (DLL) statements.
db_securityadmin: Members can modify role membership and manage permissions.
db_bckupoperator: Members can back up the database.
db_denydatareader: Members can’t view data within the database.
db_denydatawriter: Members can’t change or delete data in tables or views.
Fixed roles
The fixed server roles are applied server wide.
SysAdmin: Any member can perform any action on the server.
ServerAdmin: Any member can set configuration options on the server.
SetupAdmin: Any member can manage linked servers and SQL Server startup options and tasks.
Security Admin: Any member can manage server security.
ProcessAdmin: Any member can kill processes running on SQL Server.
DbCreator: Any member can create, alter, drop, and restore databases.
DiskAdmin: Any member can manage SQL Server disk files.
BulkAdmin: Any member can run the bulk insert command.
Some useful SQL Queries:
Get a list of users and their login type:
SELECT a.name AS LoginName
, a.type_desc AS LoginType
, a.default_database_name AS DefaultDBName,
CASE WHEN b.sysadmin = 1 THEN 'sysadmin'
WHEN b.securityadmin=1 THEN 'securityadmin'
WHEN b.serveradmin=1 THEN 'serveradmin'
WHEN b.setupadmin=1 THEN 'setupadmin'
WHEN b.processadmin=1 THEN 'processadmin'
WHEN b.diskadmin=1 THEN 'diskadmin'
WHEN b.dbcreator=1 THEN 'dbcreator'
WHEN b.bulkadmin=1 THEN 'bulkadmin'
ELSE 'Public' END AS 'ServerRole'
FROM sys.server_principals a
JOIN master..syslogins b ON a.sid = b.sid
ORDER BY 1,2,3
sp_helpuser
Get all databases and the users:
DECLARE @DBuser_sql VARCHAR(4000)
DECLARE @DBuser_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), LoginType VARCHAR(500), AssociatedRole VARCHAR(200))
SET @DBuser_sql='SELECT ''?'' AS DBName,a.name AS Name,a.type_desc AS LoginType,USER_NAME(b.role_principal_id) AS AssociatedRole FROM ?.sys.database_principals a
LEFT OUTER JOIN ?.sys.database_role_members b ON a.principal_id=b.member_principal_id
WHERE a.sid NOT IN (0x01,0x00) AND a.sid IS NOT NULL AND a.type NOT IN (''C'') AND a.is_fixed_role <> 1 AND a.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY Name'
INSERT @DBuser_table
EXEC sp_MSforeachdb @command1=@dbuser_sql
SELECT * FROM @DBuser_table ORDER BY UserName, DBName
DECLARE @Obj_sql VARCHAR(2000)
DECLARE @Obj_table TABLE (DBName VARCHAR(200), UserName VARCHAR(250), ObjectName VARCHAR(500), Permission VARCHAR(200))
SET @Obj_sql='select ''?'' as DBName,U.name as username, O.name as object, permission_name as permission from ?.sys.database_permissions
join ?.sys.sysusers U on grantee_principal_id = uid join ?.sys.sysobjects O on major_id = id WHERE ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') order by U.name '
INSERT @Obj_table
EXEC sp_msforeachdb @command1=@Obj_sql
SELECT * FROM @Obj_table
Get users in a Windows Group:
EXEC xp_logininfo 'Domain\Group';
SELECT [name]
FROM master.sys.server_principals
WHERE TYPE = 'G'
AND [name] NOT IN ('NT SERVICE\MSSQLSERVER', 'NT SERVICE\SQLSERVERAGENT')
EXEC master..xp_logininfo
@acctname = 'Domain\UserGroup',
@option = 'members'
EXEC xp_logininfo 'DOMAIN\USER_A', 'all' / 'members'
xp_logininfo [ [ @acctname = ] 'account_name' ]
[ , [ @option = ] 'all' | 'members' ]
[ , [ @privilege = ] variable_name OUTPUT]
[ @acctname = ] 'account_name'
Is the name of a Windows user or group granted access to SQL Server. account_name is sysname, with a default of NULL. If account_name is not specified, all Windows groups and Windows users that have been explicitly granted login permission are reported. account_name must be fully qualified. For example, 'ADVWKS4\macraes', or 'BUILTIN\Administrators'.
'all' | 'members'
Specifies whether to report information about all permission paths for the account, or to report information about the members of the Windows group. @option is varchar(10), with a default of NULL. Unless all is specified, only the first permission path is displayed.
[ @privilege = ] variable_name
Is an output parameter that returns the privilege level of the specified Windows account. variable_name is varchar(10), with a default of 'Not wanted'. The privilege level returned is user, admin, or null.
OUTPUT
When specified, puts variable_name in the output parameter.
SELECT * FROM sys.database_principals
SELECT login_name = sp.name
, user_name = dp.name
FROM dB.sys.database_principals AS dp
INNER JOIN sys.server_principals AS sp
ON dp.[sid] = sp.[sid];
SELECT USER_NAME(grantee_principal_id) AS User
, state_desc AS Permission
, permission_name AS Action
, CASE class
WHEN 0 THEN 'Database::' + DB_NAME()
WHEN 1 THEN OBJECT_NAME(major_id)
WHEN 3 THEN 'Schema::' + SCHEMA_NAME(major_id)
END AS Securable
FROM sys.database_permissions dp
WHERE class IN (0, 1, 3) AND minor_id = 0;
A. Listing the fixed server roles
The following query returns the list of fixed server roles.
EXEC sp_helpsrvrole
B. Listing fixed and user-defined server roles
The following query returns a list of both fixed and user-defined server roles.
SELECT * FROM sys.server_principals WHERE type = 'R' ;
Returns information about the members of a SQL Server fixed server role.
The following example lists the members of the sysadmin fixed server role.
EXEC sp_helpsrvrolemember 'sysadmin';
Displays the permissions of a fixed server role
The following query returns the permissions associated with the sysadmin fixed server role..
EXEC sp_srvrolepermission 'sysadmin';
Indicates whether a SQL Server login is a member of the specified server role.
The following example indicates whether the SQL Server login for the current user is a member of the sysadmin fixed server role.
IF IS_SRVROLEMEMBER ('public') = 1
PRINT 'Current user''s login is a member of the public role'
ELSE IF IS_SRVROLEMEMBER ('public') = 0
PRINT 'Current user''s login is NOT a member of the public role'
ELSE IF IS_SRVROLEMEMBER ('public') IS NULL
PRINT 'ERROR: The server role specified is not valid.';
The following example indicates whether the domain login Pat is a member of the diskadmin fixed server role.
SELECT IS_SRVROLEMEMBER('public', 'domain\user');
Returns one row for each member of each fixed and user-defined server role.
The following example returns the names and id's of the roles and their members.
SELECT sys.server_role_members.role_principal_id
, role.name AS RoleName
, sys.server_role_members.member_principal_id
, member.name AS MemberName
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;
Adds a login as a member of a fixed server role.
The following example adds the Windows login Corporate\HelenS to the sysadmin fixed server role.
EXEC sp_addsrvrolemember 'Corporate\HelenS', 'sysadmin';
GO
Removes a SQL Server login or a Windows user or group from a fixed server role.
The following example removes the login JackO from the sysadmin fixed server role.
EXEC sp_dropsrvrolemember 'JackO', 'sysadmin';
Creates a new user-defined server role.
role_name --Is the name of the server role to be created.
AUTHORIZATION server_principal --Is the login that will own the new server role. If no login is specified, the server role will be owned by the login that executes CREATE SERVER ROLE.
A. Creating a server role that is owned by a login
--The following example creates the server role buyers that is owned by login BenMiller.
USE master;
CREATE SERVER ROLE buyers AUTHORIZATION BenMiller;
GO
B. Creating a server role that is owned by a fixed server role
The following example creates the server role auditors that is owned the securityadmin fixed server role.
USE master;
CREATE SERVER ROLE auditors AUTHORIZATION securityadmin;
GO
Changes the membership of a server role or changes name of a user-defined server role. Fixed server roles cannot be renamed.
A. Changing the name of a server role
The following example creates a server role named Product, and then changes the name of server role to Production.
CREATE SERVER ROLE Product ;
ALTER SERVER ROLE Product WITH NAME = Production ;
GO
B. Adding a domain account to a server role
The following example adds a domain account named adventure-works\roberto0 to the user-defined server role named Production.
ALTER SERVER ROLE Production ADD MEMBER [adventure-works\roberto0] ;
C. Adding a SQL Server login to a server role
The following example adds a SQL Server login named Ted to the diskadmin fixed server role.
ALTER SERVER ROLE diskadmin ADD MEMBER Ted ;
GO
D. Removing a domain account from a server role
The following example removes a domain account named adventure-works\roberto0 from the user-defined server role named Production.
ALTER SERVER ROLE Production DROP MEMBER [adventure-works\roberto0] ;
E. Removing a SQL Server login from a server role
The following example removes the SQL Server login Ted from the diskadmin fixed server role.
ALTER SERVER ROLE Production DROP MEMBER Ted ;
GO
F. Granting a login the permission to add logins to a user-defined server role
The following example allows Ted to add other logins to the user-defined server role named Production.
GRANT ALTER ON SERVER ROLE::Production TO Ted ;
GO
G. To view role membership
To view role membership, use the Server Role (Members) page in SQL Server Management Studio or execute the following query:
SELECT SRM.role_principal_id, SP.name AS Role_Name,
SRM.member_principal_id, SP2.name AS Member_Name
FROM sys.server_role_members AS SRM
JOIN sys.server_principals AS SP
ON SRM.Role_principal_id = SP.principal_id
JOIN sys.server_principals AS SP2
ON SRM.member_principal_id = SP2.principal_id
ORDER BY SP.name, SP2.name
Removes a user-defined server role.
User-defined server roles are new in SQL Server 2012.
A. To drop a server role
The following example drops the server role purchasing.
DROP SERVER ROLE purchasing;
GO
B. To view role membership
To view role membership, use the Server Role (Members) page in SQL Server Management Studio or execute the following query:
SELECT SRM.role_principal_id, SP.name AS Role_Name,
SRM.member_principal_id, SP2.name AS Member_Name
FROM sys.server_role_members AS SRM
JOIN sys.server_principals AS SP
ON SRM.Role_principal_id = SP.principal_id
JOIN sys.server_principals AS SP2
ON SRM.member_principal_id = SP2.principal_id
ORDER BY SP.name, SP2.name
C. To view role membership
To determine whether a server role owns another server role, execute the following query:
SELECT SP1.name AS RoleOwner, SP2.name AS Server_Role
FROM sys.server_principals AS SP1
JOIN sys.server_principals AS SP2
ON SP1.principal_id = SP2.owning_principal_id
ORDER BY SP1.name ;
Contains a row for every server-level principal.
Column name Data type Description
name sysname Name of the principal. Is unique within a server.
principal_id int ID number of the Principal. Is unique within a server.
sid varbinary(85) SID (Security-IDentifier) of the principal. If Windows principal, then it
matches Windows SID.
type char(1) Principal type:
S = SQL login
U = Windows login
G = Windows group
R = Server role
C = Login mapped to a certificate
K = Login mapped to an asymmetric key
type_desc nvarchar(60) Description of the principal type:
SQL_LOGIN
WINDOWS_LOGIN
WINDOWS_GROUP
SERVER_ROLE
CERTIFICATE_MAPPED_LOGIN
ASYMMETRIC_KEY_MAPPED_LOGIN
is_disabled int 1 = Login is disabled.
create_date datetime Time at which the principal was created.
modify_date datetime Time at which the principal definition was last modified.
credential_id int ID of a credential associated with this principal. If no credential is associated
with this principal, credential_id will be NULL.
SELECT pr.principal_id, pr.name, pr.type_desc,
pe.state_desc, pe.permission_name
FROM sys.server_principals AS pr
JOIN sys.server_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id;
Role with T/SQL
You can create a user-defined server role a number of ways, including T/SQL, the SQL Server Management Studio UI and Windows PowerShell. If I were to create a user-defined server role using T/SQL, I would use three different commands. First, the Create Server Role statement would create the user-defined server role; second, the Alter Server Role would add a user to the server role; finally, the Grant statement would grant the needed rights to the role.
These three statements are shown in the following code, which outlines creating a user-defined server role called “ViewServerState.” This specifies a user added to the role called “SomeFakeLogin”and the role is granted the View Server State right. To grant additional users this right, you simply need to add those users to the fixed server role using the Alter Server Role line:
USE [master]
GO
CREATE SERVER ROLE [ViewServerState] AUTHORIZATION [sa]
GO
ALTER SERVER ROLE [ViewServerState] ADD MEMBER [SomeFakeLogin]
GO
GRANT VIEW SERVER STATE TO [ViewServerState]
GO
You'd remove a user from a user-defined server role with the Alter Server Role. Instead of using the Add Member syntax, you’d use Drop Member:
ALTER SERVER ROLE [ViewServerState] DROP MEMBER [SomeFakeLogin]
GO
create database Whatever
GO
USE Whatever
CREATE ROLE [ReallyReadOnly]
--give reader rights to this group
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [ReallyReadOnly]
--explicitly DENY access to writing
ALTER AUTHORIZATION ON SCHEMA::[DB_DenyDataWriter] TO [ReallyReadOnly]
GRANT EXECUTE TO [ReallyReadOnly]
create the Role for my Dev guys, who create tables and procs
CREATE ROLE [DEVAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverDEVAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVAdmins]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVAdmins]
GRANT EXECUTE,ALTER TO [WhateverDEVAdmins]
create role for my normal users
CREATE ROLE [DEVUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVUsers]
ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVUsers]
GRANT EXECUTE TO [WhateverDEVUsers]
now add specific users to nearly-Admins
IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')
BEGIN
EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'dBName', @deflanguage = N'us_english'
add this user to permit read and write
END
IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'jeff')
BEGIN
EXEC MASTER.dbo.sp_addlogin @loginame = N'jeff', @passwd = 'NotARealPassword', @defdb = N'dBName', @deflanguage = N'us_english'
END
USE [dBName]
make a user in the db for the matching login
CREATE USER [bob] FOR LOGIN [bob]
CREATE USER [jeff] FOR LOGIN [jeff]
add these logs to the role
EXEC sp_addrolemember N'DEVAdmins', N'bob'
EXEC sp_addrolemember N'DEVAdmins', N'jeff'
CREATE LOGIN [NT AUTHORITY\Authenticated Users] FROM WINDOWS WITH DEFAULT_DATABASE=[dBName]
CREATE USER [NT AUTHORITY\Authenticated Users] FOR LOGIN [NT AUTHORITY\Authenticated Users]
EXEC sp_addrolemember N'DEVUsers', N'NT AUTHORITY\Authenticated Users'
Creates the login AbolrousHazem with password '340$Uuxwp7Mcxo7Khy'.
CREATE LOGIN AbolrousHazem
WITH PASSWORD = '340$Uuxwp7Mcxo7Khy';
GO
Creates a database user for the login created above.
CREATE USER AbolrousHazem FOR LOGIN AbolrousHazem;
GO
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;
for more information on the function see: http://msdn.microsoft.com/en-us/library/ms186234.aspx
