script to generated the user and permission

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

script to generated the user and permission

Author

Topic

sanjnep
Posting Yak Master

191 Posts

Posted - 2005-10-03 : 18:41:54

Here is the simple script, which generated the user and user permission and can be transferred to another database. I think it may useful for new DBA. I just wrote it 2 days ago.

-----------------------------------------------------------------

declare @login varchar(20),
@db_user varchar(20),
@role varchar(30),
@role_mem varchar(30)

print 'USE '+ db_name()
print '-------------------------------------------------------------'
print '--Granting current DB access'
print '-------------------------------------------------------------'
declare add_user cursor for
select b.name as 'login', a.name as 'db_user' from sysusers as a inner join master..syslogins as b
on a.sid = b.sid and b.name <> 'sa' order by a.name,b.name
open add_user
fetch next from add_user
into @login, @db_user
while @@fetch_status = 0
begin
print 'exec sp_grantdbaccess '+''''+@login+''''+','+''''+@db_user+''''
fetch next from add_user
into @login, @db_user
end
close add_user
deallocate add_user
print '-------------------------------------------------------------'
print'--Adding role'
print '-------------------------------------------------------------'
---------add role
declare add_role cursor for
select name from sysusers where issqlrole =1 and gid <> 0
open add_role
fetch next from add_role
into @role
while @@fetch_status = 0
begin
print 'exec sp_addrole '+''''+@role+''''
fetch next from add_role into @role
end
close add_role
deallocate add_role
----------add role members
print '-------------------------------------------------------------'
print '--Adding role members'
print '-------------------------------------------------------------'
declare add_role_member cursor for
select g.name as 'role',u.name as 'name'from sysusers u, sysusers g, sysmembers m
where g.uid = m.groupuid and g.issqlrole = 1 and u.uid = m.memberuid and u.name <> 'dbo' order by g.name,u.name
open add_role_member
fetch next from add_role_member
into @role_mem,@db_user
while @@fetch_status = 0
begin
print 'exec sp_addrolemember '+''''+@role_mem+''''+','+''''+@db_user+''''
fetch next from add_role_member
into @role_mem, @db_user
end
close add_role_member
deallocate add_role_member

Sanjeev shrestha

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-10-03 : 22:16:55

nice script. I use a modified version of the one microsoft has online called sp_help_revlogin.

I think derrick modified it originally. His mod puts in the default database information. The only thing this script lacks is setting the fixed server roles. I might try to make a mod sometime later that adds this ability, right now it handles everything else pretty well.

here is the link for derrick's modified version he posted here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=35143

The original from microsoft is here: http://support.microsoft.com/default.aspx?scid=kb;en-us;246133

btw, you still need the sp_hexadecimal procedure from the original microsoft KB article for the modified sp_help_revlogin to work.

-ec

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources