Add to collection
Expert User

Expert User

Verified

Grant Access to user or logins on All databases

Grant Access to user or logins on All databases like Data Reader, Data Writer, View Server State and View Database state

Logins are created at SQL Server Instance level whereas Users are created at database level


USE [master]

GO

CREATE LOGIN [Domain\SQL_Login] FROM WINDOWS WITH DEFAULT_DATABASE=[master]

GO

use [master]

GO

GRANT VIEW ANY DATABASE TO [Domain\SQL_Login]

GO

use [master]

GO

GRANT VIEW ANY DEFINITION TO [Domain\SQL_Login]

GO

use [master]

GO

GRANT VIEW SERVER STATE TO [Domain\SQL_Login]

GO


DECLARE @dbname VARCHAR(50)   

DECLARE @statement NVARCHAR(max)

DECLARE @LoginName NVARCHAR(max)


SET @LoginName='Domain\SQL_Login';




DECLARE db_cursor CURSOR 

LOCAL FAST_FORWARD

FOR  

SELECT name

FROM MASTER.dbo.sysdatabases

OPEN db_cursor  

FETCH NEXT FROM db_cursor INTO @dbname  

WHILE @@FETCH_STATUS = 0  

BEGIN  


if @dbname='tempdb'

begin

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER ['+@LoginName+'] 

FOR LOGIN ['+@LoginName+']; EXEC sp_addrolemember N''db_datareader'',['+@LoginName+'];EXEC sp_addrolemember N''db_datawriter'',['+@LoginName+']'

end

else

begin

SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER ['+@LoginName+'] 

FOR LOGIN ['+@LoginName+']; EXEC sp_addrolemember N''db_datareader'',['+@LoginName+']'

USE [master]
GO
CREATE LOGIN [Domain\SQL_Login] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
use [master]
GO
GRANT VIEW ANY DATABASE TO [Domain\SQL_Login]
GO
use [master]
GO
GRANT VIEW ANY DEFINITION TO [Domain\SQL_Login]
GO
use [master]
GO
GRANT VIEW SERVER STATE TO [Domain\SQL_Login]
GO

DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)
DECLARE @LoginName NVARCHAR(max)

SET @LoginName='Domain\SQL_Login';



DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

if @dbname='tempdb'
begin
SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER ['+@LoginName+'] 
FOR LOGIN ['+@LoginName+']; EXEC sp_addrolemember N''db_datareader'',['+@LoginName+'];EXEC sp_addrolemember N''db_datawriter'',['+@LoginName+']'
end
else
begin
SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER ['+@LoginName+'] 
FOR LOGIN ['+@LoginName+']; EXEC sp_addrolemember N''db_datareader'',['+@LoginName+']'
end


select @statement
exec sp_executesql @statement

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

Comments 0

  • Sorry!! No comment posted yet. Become the first user to give comment.
Add a comment

Would you like to write for w3 sniff?

Become part of an amazing group of contributors and authors and start writing for us

Join Now
USE [master]
GO
CREATE LOGIN [Domain\SQL_Login] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO
use [master]
GO
GRANT VIEW ANY DATABASE TO [Domain\SQL_Login]
GO
use [master]
GO
GRANT VIEW ANY DEFINITION TO [Domain\SQL_Login]
GO
use [master]
GO
GRANT VIEW SERVER STATE TO [Domain\SQL_Login]
GO

DECLARE @dbname VARCHAR(50)   
DECLARE @statement NVARCHAR(max)
DECLARE @LoginName NVARCHAR(max)

SET @LoginName='Domain\SQL_Login';



DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR  
SELECT name
FROM MASTER.dbo.sysdatabases
OPEN db_cursor  
FETCH NEXT FROM db_cursor INTO @dbname  
WHILE @@FETCH_STATUS = 0  
BEGIN  

if @dbname='tempdb'
begin
SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER ['+@LoginName+'] 
FOR LOGIN ['+@LoginName+']; EXEC sp_addrolemember N''db_datareader'',['+@LoginName+'];EXEC sp_addrolemember N''db_datawriter'',['+@LoginName+']'
end
else
begin
SELECT @statement = 'use '+@dbname +';'+ 'CREATE USER ['+@LoginName+'] 
FOR LOGIN ['+@LoginName+']; EXEC sp_addrolemember N''db_datareader'',['+@LoginName+']'
end


select @statement
exec sp_executesql @statement

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

Feedback submitted successfully.