Grant User Access to All SQL Server Databases

By: Ahmad Yaseen | Comments (15) | Related: > Security

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'[email protected]', 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 '[email protected] +';'+ '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:

Next Steps

Check out the Cursor options.
Review the SQL Server Security Tips.

About the author

Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

View all my tips

Comments For This Article

Wednesday, January 20, 2021 - 11:48:23 AM - Joel Foudy

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

Thursday, July 16, 2020 - 5:56:21 AM - AJD

Thanks, very useful. I made a couple of small changes though:

Changed size of @dbname to 255 as one of my db names was larger

added square braces '[' , ']' around the @dbname in the generated sql statement as I had an issue with a db name.

added '(status & 1024) = 0' to the where clause to exclude those dbs which are set to read-only.

Sunday, April 5, 2020 - 5:45:22 AM - wahid

Thank you, very usefull scripts.

Wednesday, December 4, 2019 - 12:53:23 PM - rink	Back To Top (83285)
It worked smoothly as expected, thanks!!

Sunday, June 24, 2018 - 1:48:33 AM - Ahmad Yaseen

Hello Dan,

Kindly note that running this script each time will include all avialble databases, as it will always reads from sysdatabases that has all new and old databases.

Best Regards,

Ahmad

Thursday, June 21, 2018 - 12:31:11 PM - Dan Petitt

None of those examples work for me as my connection has to have permissions to open that database to be able to add the login to it negating the point of the login in the first place.

I have a dynamic list of databases, i want to add a login which can do updates on the database but i dont know if knew databases might exist ahead of time

Sunday, June 4, 2017 - 2:36:39 AM - Ahmad Yaseen

Hi Mathy,

Thank you for your input here. Please note that there is no way to get such information unless an auditing tool such as the built-in SQL Audit tool is enabled and monitoring the SQL Server access.

Best Regards,

Ahmad

Thursday, June 1, 2017 - 11:26:25 AM - Mathy

Hi ,

i have granted read,write permission to the user.but some has changed to deny_reader,deny_writer.

so my question is ,how can we came to know who granted the access to the user? .please share your comments . thanks

Tuesday, March 14, 2017 - 6:05:59 AM - mancio

based on my experience

please consider some small guidelines:

a. user logins might have spaces or special characters in, so better *always* use QUOTENAME function

b. same as above for database names

c. login existance *should* be verified before adding a new login to sql server (also for re-use purposes)

d. user existance *should* be verified befora adding a new user (also for re-use purposes)

e. @loginName and @dbname *should* be declared as SYSNAME for better understanding what's goin' on and due to the Sql Server specifications

f. checking for the database name in order to exclude system databases is not the best approach, better using [owner_id] (1 means system)

Few tips:

a. using sp_executesql (a well known not highly documented stored proc) is not necessary: better use EXEC(@statement) to avoid any compatibility issues

b. adding a PRINT statement could help in where executing so *delicated* instructions

c. always using the @statement variable approach could could be used in order to create a stored proc rather than having to execute a sql file if you need more than once

d. adding some small checks (@debug, @dataReader, @dataWriter) enforces the knowledge where executing the script/proc

e. please also consider I'm not checking for database availability/readonly/singleuser etc... that *should* also be added ^_^''

hope this is helpful ;-)

USE [master]

DECLARE @debug BIT = 1

DECLARE @dataReader BIT = 1

DECLARE @dataWriter BIT = 0

DECLARE @loginName SYSNAME = 'myNewLogin'

DECLARE @loginPass NVARCHAR(50) = 'myNewLoginPassword'

DECLARE @statement NVARCHAR(MAX)

IF NOT EXISTS(SELECT lg.* FROM syslogins AS lg WHERE lg.name = @loginName) BEGIN

SET @statement = 'CREATE LOGIN ' + QUOTENAME(@loginName) + ' WITH PASSWORD=N''' + @loginPass + ''', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON;'

PRINT @statement

IF (@debug = 0) EXEC(@statement)

END

DECLARE @dbname SYSNAME

DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR SELECT db.name FROM master.sys.databases AS db WHERE db.owner_sid != 1 ORDER BY db.name

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @dbname

WHILE @@FETCH_STATUS = 0 BEGIN

SET @statement = 'USE ' + QUOTENAME(@dbname) +';' + CHAR(13) + CHAR(10)

+ 'IF NOT EXISTS (SELECT * FROM sysusers AS u WHERE u.name = ''' + @loginName + ''') BEGIN' + CHAR(13) + CHAR(10)

+ ' CREATE USER ' + QUOTENAME(@loginName) + ' FOR LOGIN ' + QUOTENAME(@loginName) + ';' + CHAR(13) + CHAR(10)

SET @statement += 'END' + CHAR(13) + CHAR(10)

IF (@dataReader = 1 OR @dataWriter = 1) SET @statement += 'EXEC sp_addrolemember N''db_datareader'', ' + QUOTENAME(@loginName) + ';' + CHAR(13) + CHAR(10)

IF (@dataWriter = 1) SET @statement += 'EXEC sp_addrolemember N''db_datawriter'', ' + QUOTENAME(@loginName) + ';' + CHAR(13) + CHAR(10)

PRINT @statement

IF (@debug = 0) EXEC(@statement)

FETCH NEXT FROM db_cursor INTO @dbname

END

CLOSE db_cursor

DEALLOCATE db_cursor

Thursday, July 28, 2016 - 4:49:41 PM - sqlnoob

Great tip! I would mention that if you have databases with odd names, like "Test-DB", you will need to bracket the "use" statement below just as you bracket the user/login.

SELECT @statement = 'use '[email protected] +';'+ 'CREATE USER [TipsDemoUser] 
FOR LOGIN [TipsDemoUser]; EXEC sp_addrolemember N''db_datareader'', 
[TipsDemoUser];EXEC sp_addrolemember N''db_datawriter'', [TipsDemoUser]'

would become

SELECT @statement = 'use ['[email protected] +'];'+ 'CREATE USER [TipsDemoUser] 
FOR LOGIN [TipsDemoUser]; EXEC sp_addrolemember N''db_datareader'', 
[TipsDemoUser];EXEC sp_addrolemember N''db_datawriter'', [TipsDemoUser]'

Tuesday, October 27, 2015 - 10:18:03 AM - Adam Gojdas

@Tarek & @Amrut Kumbar

sp_MSforeachdb utilizes a cursor. Take a look and you will see:

    exec(N'declare hCForEachDatabase cursor global for select name from master.dbo.sysdatabases d ' +
            N' where (d.status & ' + @inaccessible + N' = 0)' +
            N' and (DATABASEPROPERTYEX(d.name, ''UserAccess'') <> ''SINGLE_USER'' and (has_dbaccess(d.name) = 1))' )

That cursor is used by the called proc sys.sp_MSforeach_worker.

Considering this is a global cursor the use of the proc sp_MSforeachdb should be done with care I would think. There is potential for problems because global cursors can be accessed by anyone at any time during their execution time.

Tuesday, June 30, 2015 - 10:06:27 AM - Graham Day

sp_MSforeachdb is an undocumented and unsupported piece of code, and has not been maintained for many years.

It is known to break under certain circumstances (particular characters in DB names), and to return incorrect results in others.

A wise DBA will not use sp_MSforeachdb.

Monday, June 29, 2015 - 3:18:29 PM - Golam Kabir	Back To Top (38065)
useful tip - thanks

Thursday, June 25, 2015 - 8:28:54 AM - Tarek	Back To Top (38034)
you can avoid using cursor by using sp_MSforeachdb

Thursday, June 25, 2015 - 5:31:11 AM - Amrut Kumbar

We can do this also in the below manner. It will take less time to execute than the cursor.

--After creating Login [TipsDemoUser1], use the below code to add user in all dbs.

declare @stmt varchar(2000),@UserName varchar(50)='TipsDemoUser1';

set @stmt='IF ''?'' NOT IN (''tempDB'',''model'',''msdb'')

BEGIN

use ?

CREATE USER ['[email protected]+']

FOR LOGIN ['[email protected]+'];

EXEC sp_addrolemember N''db_datareader'',['[email protected]+'];

EXEC sp_addrolemember N''db_datawriter'', ['[email protected]+']

END;';

--PRINT @stmt

EXEC sp_MSforeachdb @stmt

Grant User Access to All SQL Server Databases

Problem

Solution

SQL Server CREATE LOGIN Example

Grant User Access to All SQL Server Databases

Next Steps

About the author

Comments For This Article

Related Articles