May 19, 2010 at 5:01 am
Hi!!
How to create a sql login with access to all the existing as well as creating databases in a server.
Thanks in advance!!
May 19, 2010 at 6:07 am
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
May 19, 2010 at 9:00 pm
Thanks.
But Server Role sysadmin allows login to do anything on the server. I just want that login can access all the databases.
June 2, 2010 at 5:35 am
Still waiting........
June 3, 2010 at 10:49 am
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
June 11, 2010 at 7:13 am
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
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy