locked
Database permission to all the users RRS feed

  • Question

  • How can I assign permission to new database for all the existing user in SQL.?
    Sunday, May 31, 2015 1:50 AM

Answers

  • Permissions for read only access can be granted to each user with the db_datareader role. Modification privileges can be granted with db_datawriter. Ownership can be given by granting DBO. 

    I would avoid using these and instead only give permissions to the objects that the users will need. For example: DBO allows the user to backup the database.

    Roles can be added in SSMS (Management Studio) or can be added via sp_addrolemember. 

    Permissions can be granted or revoked with the GRANT or REVOKE statements.

    The following query would create a script that can be executed to add every SQL and Windows login to the new database and add each of them to the db_datareader role.

    *AGAIN I WOULD NOT DO THIS BLINDLY. SECURITY IS VERY IMPORTANT AND YOU MAY NOT WANT EVERY USER TO HAVE ACCESS TO THE DATA*

    SELECT CONCAT('CREATE USER [', name, '] FOR LOGIN [', name, ']; ALTER ROLE [db_datareader] ADD MEMBER [', name, '];')
    FROM sys.server_principals 
    WHERE type in ('S', 'U')


    Sunday, May 31, 2015 2:34 AM
  • Here is an improved version of Daniel's query. It uses quotename to add the brackets (in case some user has a right bracket in the name), and it filters out users that for one reason or another are not meaningful to add. It also add Windows groups, which certainly falls into the spec.

    Like Daniel I echo the sentiment that this should be done with care, but I guess that there are context where this makes sense.

    The query requires SQL 2012. The SQL 2008 version is left as an exercise to the reader.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, May 31, 2015 9:00 AM

All replies

  • Permissions for read only access can be granted to each user with the db_datareader role. Modification privileges can be granted with db_datawriter. Ownership can be given by granting DBO. 

    I would avoid using these and instead only give permissions to the objects that the users will need. For example: DBO allows the user to backup the database.

    Roles can be added in SSMS (Management Studio) or can be added via sp_addrolemember. 

    Permissions can be granted or revoked with the GRANT or REVOKE statements.

    The following query would create a script that can be executed to add every SQL and Windows login to the new database and add each of them to the db_datareader role.

    *AGAIN I WOULD NOT DO THIS BLINDLY. SECURITY IS VERY IMPORTANT AND YOU MAY NOT WANT EVERY USER TO HAVE ACCESS TO THE DATA*

    SELECT CONCAT('CREATE USER [', name, '] FOR LOGIN [', name, ']; ALTER ROLE [db_datareader] ADD MEMBER [', name, '];')
    FROM sys.server_principals 
    WHERE type in ('S', 'U')


    Sunday, May 31, 2015 2:34 AM
  • Here is an improved version of Daniel's query. It uses quotename to add the brackets (in case some user has a right bracket in the name), and it filters out users that for one reason or another are not meaningful to add. It also add Windows groups, which certainly falls into the spec.

    Like Daniel I echo the sentiment that this should be done with care, but I guess that there are context where this makes sense.

    The query requires SQL 2012. The SQL 2008 version is left as an exercise to the reader.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, May 31, 2015 9:00 AM