SQL Server - Give a Login Permission for Read Access to All Existing and Future Databases

SQL Server - Give a Login Permission for Read Access to All Existing and Future Databases

For new databases, add the user in the model database. This is used as the template for all new databases.

USE model
CREATE USER ... FROM LOGIN...
EXEC sp_addrolemember 'db_datareader', '...'

For existing databases, use sp_MSForEachDb

EXEC sp_MSForEachDb '
 USE ?
 CREATE USER ... FROM LOGIN...  
 EXEC sp_addrolemember ''db_datareader'', ''...''
'

How to grant users read-only access to all databases

add the user to the db_datareader role

example

exec sp_addrolemember 'db_datareader',YourLogin

Info about db_datareader: http://msdn.microsoft.com/en-us/library/ms188629(SQL.90).aspx

SQL Server query to find all permissions/access for all users in a database

This is my first crack at a query, based on Andomar's suggestions. This query is intended to provide a list of permissions that a user has either applied directly to the user account, or through
roles that the user has.

/*
Security Audit Report
1) List all access provisioned to a sql user or windows user/group directly 
2) List all access provisioned to a sql user or windows user/group through a database or application role
3) List all access provisioned to the public role

Columns Returned:
UserName        : SQL or Windows/Active Directory user account.  This could also be an Active Directory group.
UserType        : Value will be either 'SQL User' or 'Windows User'.  This reflects the type of user defined for the 
                  SQL Server user account.
DatabaseUserName: Name of the associated user as defined in the database user account.  The database user may not be the
                  same as the server user.
Role            : The role name.  This will be null if the associated permissions to the object are defined at directly
                  on the user account, otherwise this will be the name of the role that the user is a member of.
PermissionType  : Type of permissions the user/role has on an object. Examples could include CONNECT, EXECUTE, SELECT
                  DELETE, INSERT, ALTER, CONTROL, TAKE OWNERSHIP, VIEW DEFINITION, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
PermissionState : Reflects the state of the permission type, examples could include GRANT, DENY, etc.
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ObjectType      : Type of object the user/role is assigned permissions on.  Examples could include USER_TABLE, 
                  SQL_SCALAR_FUNCTION, SQL_INLINE_TABLE_VALUED_FUNCTION, SQL_STORED_PROCEDURE, VIEW, etc.   
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.          
ObjectName      : Name of the object that the user/role is assigned permissions on.  
                  This value may not be populated for all roles.  Some built in roles have implicit permission
                  definitions.
ColumnName      : Name of the column of the object that the user/role is assigned permissions on. This value
                  is only populated if the object is a table, view or a table value function.                 
*/

--List all access provisioned to a sql user or windows user/group directly 
SELECT  
    [UserName] = CASE princ.[type] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],       
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[type] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],   
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{All Users}',       
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]                   
JOIN 
    --All objects   
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc] 

SQL user permissions in 2 databases

As long as your user is defined at the instance level, you can grant them whatever rights they need in each database. Create your users under the master, and then add them as users of each database granting them whatever rights are needed per the specific DB.

EDITED with more detail (based on comments):

First, you need to create a Database Login. This is a login that can then be added as a user to one or more databases on your instance.

Details Here (including SQL syntax): http://technet.microsoft.com/en-us/library/ms189751.aspx

Second, you need to provision the user in the database (Database1, Database2, etc...). You will want to have these users added identically to each of these product databases that gets installed. As long as the user is there, you will be able to query across the various DBs on your instance.

Details Here (Including SQL): http://technet.microsoft.com/en-us/library/aa337545.aspx

Good Luck!

Set permissions on newly created databases with rules

You can implement a DDL trigger that will be fired whenever a new database is created. Depending on the properties of the database, like name or storage definition, you can probably run additional scripts on the new database to set up the required security.

http://msdn.microsoft.com/en-us/library/ms186406.aspx

Here's a snippet from the article above:

IF EXISTS (SELECT * FROM sys.server_triggers
    WHERE name = 'ddl_trig_database')
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO
CREATE TRIGGER ddl_trig_database 
ON ALL SERVER 
FOR CREATE_DATABASE 
AS 
    PRINT 'Database Created.'
    SELECT EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)')
GO
DROP TRIGGER ddl_trig_database
ON ALL SERVER;
GO

Regards

Piotr

How to grant db_owner permissions to an application role?

"You cannot place an application role into a database role" appears to be the part that's not correct. An (application) role can be added as a member of another role:

EXEC sp_addrolemember 'db_owner', 'ApplicationRoleName';

(From 2012 onwards, this procedure has been deprecated in favor of the new ALTER ROLE .. ADD MEMBER syntax.)

To GRANT CONTROL on an entire database to a role, the following will do:

USE [DatabaseName];
GRANT CONTROL ON DATABASE::[DatabaseName] TO [ApplicationRoleName];

The USE is necessary to bring the role in scope; the DATABASE:: scope qualifier is always necessary when referencing databases.

Having said all that, an application role is probably not a good candidate to grant the broadest of permissions to. The password for it is passed in plaintext unless care is taken to encrypt the connection itself, monitoring and auditing may overlook it, and it's easy to forget to revert when the permissions are no longer needed. That last part is extra insidious because an unreverted app role activation will persist across pooled connections, leaving a connection "stuck" in admin mode. Alternatives include opening a separate connection with new credentials for arbitrary actions and using stored procedures with EXECUTE AS for permissions that can't be GRANTed effectively.

Permission rights for SQL login accessing Database for ASP.Net Application

Well, it depends on how complicated you want to make it :-)

Simplest solution:

• make your login / db user have the db_datareader role to read all tables
• make your login / db user have the db_datawriter role to write all tables

As for executing stored procs, what we did is create a new custom database role "db_executor" in our database like this:

CREATE ROLE [db_executor] AUTHORIZATION [dbo]
GRANT EXECUTE TO [db_executor]

and then we grant this role to the db user as well. This new custom database role will have execute rights on all existing AND on all future stored procs/funcs in your database.

With this, your db user can read and write any table and execute any stored proc and stored func.

More complex solution:
You can of course also GRANT permissions on individual tables, views, procs, funcs to inidividual db users and/or db roles. But it can get quite messy and complicated.

Marc

---

Related Topics