May 10, 2004 at 4:16 pm
I need some advise cleaning up group\user accounts.
The WHS script I am creating needs to advise the administrator the kind of access each group/user has (read,write,etc) for which database. I have run several sp's and sys objects, which provide alot of useful information. However, I am having trouble putting it all together.
I'm apporaching this looking at "Security - Logins" and "Databases - database - users". Is this a viable approach?
Thanks
May 10, 2004 at 8:20 pm
Try this. First replace the [UserName/RoleName] with the user you want or pass '' to get all users. Replace [DB NAME] with the DB you want to get the user information from.
You can run this on each DB manually to get the results you want or you can create a cursor to go through sysdatabases and call below script for each db.
use master
declare @User_or_Role as sysname
set @User_or_Role = '[UserName/RoleName]'
create table #Actions (ActionID tinyint, Name nvarchar(50) )
insert into #Actions (ActionID, Name) Values(26 , 'REFERENCES')
insert into #Actions (ActionID, Name) Values(178 , 'CREATE FUNCTION')
insert into #Actions (ActionID, Name) Values(193 , 'SELECT')
insert into #Actions (ActionID, Name) Values(195 , 'INSERT')
insert into #Actions (ActionID, Name) Values(196 , 'DELETE')
insert into #Actions (ActionID, Name) Values(197 , 'UPDATE')
insert into #Actions (ActionID, Name) Values(198 , 'CREATE TABLE')
insert into #Actions (ActionID, Name) Values(203 , 'CREATE DATABASE')
insert into #Actions (ActionID, Name) Values(207 , 'CREATE VIEW')
insert into #Actions (ActionID, Name) Values(222 , 'CREATE PROCEDURE')
insert into #Actions (ActionID, Name) Values(224 , 'EXECUTE')
insert into #Actions (ActionID, Name) Values(228 , 'BACKUP DATABASE')
insert into #Actions (ActionID, Name) Values(233 , 'CREATE DEFAULT')
insert into #Actions (ActionID, Name) Values(235 , 'BACKUP LOG')
insert into #Actions (ActionID, Name) Values(236 , 'CREATE RULE')
select su.name as User_Or_Role, so.name as Object, tmp.Name as Permission
from [DB NAME].dbo.sysprotects sp join [DB NAME].dbo.sysusers su
on sp.uid = su.uid
join #Actions tmp
on sp.action = tmp.ActionID
join [DB NAME].dbo.sysobjects so
on sp.id = so.id
where @User_or_Role = '' or su.name = @User_or_Role
order by su.name, so.name, tmp.Name
drop table #Actions
May 11, 2004 at 7:13 am
Thanks. This works great
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply