Help with sql scripting.
-
Hi friends!
I need to create a user login around 20 SQLServers, the user needs to be owner of all database, except the system databases.
Can anyone help me with the scripting?
Thanks all.
-
this seems help me too, thanks in advance
-
Include dbo to Model-db so it is included in every new db that will be created.
Try this
_test_test_test_...
-- jobi dd 19/03/2004
-- toevoegen YourDomain\YourDBOUser aan server en met db_owner-rechten op alle niet-systeem-db
set nocount on
use master
go
declare @WinOrSQLId char(3)
set @WinOrSQLId = 'Win'
If @WinOrSQLId = 'Win'
begin
-- Toevoegen YourDomain\YourDBOUser aan Server
if not exists (select * from master.dbo.syslogins where loginname = N'YourDomain\YourDBOUser')
BEGIN
Print '-- SQLServer [' + @@Servername + '] '
exec sp_grantlogin N'YourDomain\YourDBOUser'
exec sp_defaultdb N'YourDomain\YourDBOUser', N'master'
exec sp_defaultlanguage N'YourDomain\YourDBOUser', N'us_english'
end
else
begin
-- Toevoegen SQLuser YourDBOUser aan Server
if not exists (select * from master.dbo.syslogins where loginname = N'yourdbologin' )
BEGIN
Print '-- SQLServer [' + @@Servername + '] '
Exec sp_addlogin @loginame = 'yourdbologin'
, @passwd = 'password'
, @defdb = 'database'
end
end
GO
Create table ##TMP_Db
(DbName varchar(128) not null primary key)
insert into ##TMP_Db
select name
from master.dbo.sysdatabases
where case when convert(binary(2),status ) & 512 = 512 then 0 -- Offline
when convert(binary(2),status ) & 1024 = 1024 then 0 -- ReadOnly
else 1
end = 1
and name not in ('master','msdb', 'tempdb')
Declare csrDb cursor for
select DbName
from ##TMP_Db
for read only
declare @DbName varchar(128)
declare @sqlCmd varchar(5000)
Open csrDb
FETCH NEXT FROM csrDb INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN
if @WinOrSQLId = 'Win'
begin
Select @sqlCmd = 'use ' + @DbName + ' ' + char(13)
+ 'if not exists (select * from dbo.sysusers where name = N''YourDBOUser'' )' + char(13)
+ 'BEGIN' + char(13)
+ ' Print ''-- SQLServer [' + @@Servername + '] - db [' + @DbName + ']'' ' + char(13)
+ ' EXEC sp_grantdbaccess N''YourDomain\YourDBOUser'', N''YourDBOUser'' ' + char(13)
+ ' EXEC sp_addrolemember N''db_owner'', N''YourDBOUser'' ' + char(13)
+ 'END'
end
Else
begin
Select @sqlCmd = 'use ' + @DbName + ' ' + char(13)
+ 'if not exists (select * from dbo.sysusers where name = N''YourDBOUser'' )' + char(13)
+ 'BEGIN' + char(13)
+ ' Print ''-- SQLServer [' + @@Servername + '] - db [' + @DbName + ']'' ' + char(13)
+ ' EXEC sp_grantdbaccess N''yourdbologin'', N''yourdbologin'' ' + char(13)
+ ' EXEC sp_addrolemember N''db_owner'', N''yourdbologin'' ' + char(13)
+ 'END'
end
-- print @sqlCmd
exec (@sqlCmd)
--read next
FETCH NEXT FROM csrDb INTO @DbName
END
-- Cursor afsluiten
CLOSE csrDb
DEALLOCATE csrDb
drop table ##TMP_Db
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for help!!!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply