Last active
December 28, 2015 19:09
-
-
Save derekmurawsky/7548383 to your computer and use it in GitHub Desktop.
SQL statement to add a local Windows user to SQL server, and then add them to all databases as a datareader. Note: uses sp_addrolemember which is depreciated in post-2012. I haven't found a way to do this manually in < 2012 as the alter role command doesn't support adding a user.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @UserName sysname; | |
DECLARE @NameToAdd sysname; | |
DECLARE @S nvarchar(1000) | |
SET @UserName = 'User Name'; | |
SET @NameToAdd = @@SERVERNAME + '\' + @UserName; | |
-- Create a login using windows auth | |
--set @s = 'CREATE LOGIN ' + quotename(@NameToAdd) + ' FROM WINDOWS;' | |
--exec (@S) | |
-- Add user to all databases as db_datareader | |
set @S = 'Use [?]; CREATE USER ' + quotename(@NameToAdd) + ' FOR LOGIN ' + quotename(@NameToAdd) + '; exec sp_addrolemember ''db_datareader'' , ' + quotename(@NameToAdd) + ';'; | |
Exec sp_MSforeachdb @command1 = @S; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment