Grant User Access to All SQL Server Databases

Problem

Consider a situation when you have a large number of databases on your SQL Server, and you are requested to grant user access to all SQL Server databases. How can you grant access to a user for all databases on a SQL Server instance?

Solution

You have a few different options, in SQL Server Management Studio, you can tick each checkbox for all databases from the user mapping interface in the login properties to grant the access.  This can take a long time to finish due to the large number of databases.  The solution that I prefer, is to create a script that loops through all the databases and grants the user the requested access to each database.  In this example, I will use a SQL Server Cursor which is a database object that is used to manipulate data on a row-by-row basis, instead of the typical T-SQL command that operates on all the rows at one time. In this example, I use a simple cursor with the LOCAL and FAST_FORWARD options to optimize its performance.  Here is a brief explanation of these options:

  • LOCAL – Specifies that the cursor can be available only in the batch in which the cursor was created, and will be de-allocated once the batch terminates.
  • FAST_FORWARD – Specifies that the cursor produces the least amount of overhead on SQL Server by choosing the lowest cost static or dynamic plan.

SQL Server CREATE LOGIN Example

Let’s begin with creating a SQL Server login using the T-SQL code below:

USE [master]
GO
CREATE LOGIN [TipsDemoUser] WITH PASSWORD=N'Tips_DemoUser@123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
GO

Grant User Access to All SQL Server Databases

Based on the code above that we need to generate, let’s exaplain the cursor code:

  • Step 1 – Get a list of all user databases on our SQL Server instance, excluding the system databases (master, model, msdb, tempdb and distribution) from the sysdatabases table.
  • Step 2 – Once the databases list is ready, loop through these database to create a user for that login and grant it read and write access on each database.
  • Step 3 – Execute the string that was generated to create the user and grant permissions
Use master
GO
DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)
DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb','distribution')  
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  
SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER [TipsDemoUser] 
FOR LOGIN [TipsDemoUser]; EXEC sp_addrolemember N''db_datareader'', 
[TipsDemoUser];EXEC sp_addrolemember N''db_datawriter'', [TipsDemoUser]'
exec sp_executesql @statement
FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

After executing the above script successfully, you can check the granted access from the Login Properties dialog box in SQL Server Management Studio below by expanding the Server Security and Logins nodes:

Check the granted access from the Login Properties dialog box

Next Steps

Related Posts

One comment

  1. Guys, just use this script. You can enter the user you want to add using the @UserName parm, and also use the config parms to choose if you want DataReader and Writer. It defaults to printing the results so you can script it. Also it checks if the user already exists in the database and adds it dynamically if needed.

    Use master
    GO

    –CONFIG
    DECLARE @UserName VARCHAR(50) = ‘UserName’
    DECLARE @PrintOnly bit = 1
    DECLARE @Add_DataReader bit = 1
    DECLARE @Add_DataWriter bit = 1

    ——————————————-
    DECLARE @dbname VARCHAR(50)
    DECLARE @statement NVARCHAR(max)
    DECLARE @ParmDefinition NVARCHAR(500);
    DECLARE @UserExists bit

    DECLARE db_cursor CURSOR
    LOCAL FAST_FORWARD
    FOR
    SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN (‘master’,’model’,’msdb’,’tempdb’,’distribution’)
    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @dbname
    WHILE @@FETCH_STATUS = 0
    BEGIN

    –Check if user already exists in the database
    SET @statement = N’USE ‘ + QUOTENAME(@dbname) + N’;
    IF EXISTS (SELECT [name]
    FROM [sys].[database_principals]
    WHERE [name] = ”’ + @UserName + ”’)
    set @UserExistsOUT = 1
    else
    set @UserExistsOUT = 0′
    SET @ParmDefinition = N’@UserExistsOUT bit OUTPUT’;

    EXEC sp_executesql @statement, @ParmDefinition, @UserExistsOUT = @UserExists OUTPUT

    —-Start Creating Script
    SET @statement = ‘use ‘+ @dbname +’;’

    –If user doesn’t exist then add CREATE statement
    IF @UserExists = 0
    BEGIN
    SET @statement = @statement + char(10) + ‘CREATE USER [‘+ @UserName +’ FOR LOGIN ‘+ @UserName +’];’
    END

    IF @Add_DataReader = 1
    SET @statement = @statement + char(10) + ‘EXEC sp_addrolemember N”db_datareader”, [‘+ @UserName +’];’

    IF @Add_DataWriter = 1
    SET @statement = @statement + char(10) + ‘EXEC sp_addrolemember N”db_datawriter”, [‘+ @UserName +’];’

    IF @PrintOnly = 1
    PRINT @statement
    ELSE
    exec sp_executesql @statement

    FETCH NEXT FROM db_cursor INTO @dbname
    END
    CLOSE db_cursor
    DEALLOCATE db_cursor

Leave a Reply

Your email address will not be published. Required fields are marked *