Create a sql login with access to all the databases

Question

Create a sql login with access to all the databases

Tarun Jaggi

Hall of Fame

Points: 3365
More actions
May 19, 2010 at 5:01 am

#386963

Hi!!
How to create a sql login with access to all the existing as well as creating databases in a server.
Thanks in advance!!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323527 More actions · Answer 1

just like sa and Builtin\Administrators, and login you create that is part of the sysadmin will inherit the ability to do anything to any database.

CREATE LOGIN [superman] WITH PASSWORD=N'NotARealPassword',

DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON

GO

EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'

GO

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tarun Jaggi Hall of Fame Points: 3365 More actions · Answer 2

Thanks.

But Server Role sysadmin allows login to do anything on the server. I just want that login can access all the databases.

Tarun Jaggi Hall of Fame Points: 3365 More actions · Answer 3

Tarun Jaggi

Hall of Fame

Points: 3365

June 2, 2010 at 5:35 am

#1175218

Still waiting........

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

Create the login (either sql or windows), and then run this. Copy & paste the output and exec. There's probably more efficient ways, but I still use cursors for small things like this. This will grant db_owner to all current databases (other than master, msdb, tempdb) and also to Model, which means all new databases created will have the user already added. Note that restoring a database will not use the Model structure, so you'll have to add the user manually again (or run this script).

declare @sql nvarchar(2000)

declare @dbname nvarchar(500)

declare GrantAccess cursor for

SELECT NAME FROM master.sys.databases WHERE database_id > 4 or name = 'model'

open GrantAccess

fetch next from GrantAccess into @dbname

while @@fetch_status = 0

BEGIN

select @sql = 'USE ['+@dbname+']

GO

EXEC sp_addrolemember N''db_owner'', N''LOGIN_HERE''

GO

'

print @sql

fetch next from GrantAccess into @dbname

END

close GrantAccess

deallocate GrantAccess

Jon Spink SSCarpal Tunnel Points: 4010 More actions · Answer 5

Thanks for this - solved my problem.

You could also insert the following if the login doesn't yet exist in each database

CREATE USER LOGIN_HERE

GO

Create a sql login with access to all the databases

Cookies on SQLServerCentral