Category Archives: Grant

How to find permissions granted on user in a database?

Posted on by
Reply

Run the following SQL to find permissions granted on users on the database

select
case when p.protecttype = 205 then 'GRANT'
when p.protecttype = 206 then 'DENY'
else 'unknown'
end + ' ' +
case when p.action = 193 then 'SELECT'
when p.action = 197 then 'UPDATE'
when p.action = 195 then 'INSERT'
when p.action = 196 then 'DELETE'
when p.action = 224 then 'EXECUTE'
else 'unknown'
end + ' ON ' + objectowner.name + '.' + o.NAME + ' TO ' + u.name
from sysusers u
inner join sysprotects p on u.uid = p.uid
inner join sysobjects o on o.id = p.id
inner join sysusers objectowner on o.uid = objectowner.uid
WHERE u.name = 'username'
order by o.name, u.name

Advertisements

How to find permissions granted on individual objects in a database?

Posted on by
Reply

Run the following SQL to find permissions granted on individual objects on the database

select
case when p.protecttype = 205 then 'GRANT'
when p.protecttype = 206 then 'DENY'
else 'unknown'
end + ' ' +
case when p.action = 193 then 'SELECT'
when p.action = 197 then 'UPDATE'
when p.action = 195 then 'INSERT'
when p.action = 196 then 'DELETE'
when p.action = 224 then 'EXECUTE'
else 'unknown'
end + ' ON ' + objectowner.name + '.' + o.NAME + ' TO ' + u.name
from sysusers u
inner join sysprotects p on u.uid = p.uid
inner join sysobjects o on o.id = p.id and o.name = 'object_name'
inner join sysusers objectowner on o.uid = objectowner.uid
order by o.name, u.name

How to list the access the roles the different users have?

Posted on by
Reply

— print usernames and the roles assigned to the user, it can be run against any database and it will print the users and the roles assigned to different users
SELECT sl.name, g.name
FROM sysusers u, sysusers g, sysmembers m, master.dbo.syslogins sl
where g.name IN (SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1))
and g.uid = m.groupuid
and sl.sid = u.sid
and g.issqlrole = 1
and u.uid = m.memberuid
and sl.denylogin = 0
order by sl.name

Sample output:
localuser db_owner
localuser db_datareader
domain\ADuser db_datareader
domain\ADuser db_denydatawriter
….

How to create SQL scripts to recreate grants on a database granted through a role?

Posted on by
Reply

DECLARE @role_name VARCHAR(100)
DECLARE @member_name VARCHAR(100)
DECLARE @rolescur CURSOR
DECLARE @rolememberscur CURSOR

DECLARE @loginname VARCHAR(100)
DECLARE @username VARCHAR(100)
DECLARE @logincur CURSOR

PRINT '– Grant user access'
SET @logincur = CURSOR FOR SELECT l.name, u.name
FROM master..sysxlogins l, sysusers u
WHERE l.sid = u.sid AND l.name <> 'sa'
OPEN @logincur
FETCH NEXT FROM @logincur INTO @loginname, @username
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'EXEC sp_grantdbaccess ''' + @loginname + ''', ''' + @username + ''''
FETCH NEXT FROM @logincur INTO @loginname, @username
END
CLOSE @logincur
DEALLOCATE @logincur

— cursor for roles (user and built-in)
SET @rolescur = CURSOR FOR SELECT name FROM dbo.sysusers WHERE ([issqlrole] = 1 OR [isapprole] = 1) AND name <> 'db_owner'

— loop through roles
OPEN @rolescur
FETCH NEXT FROM @rolescur INTO @role_name
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT '— ' + @role_name
— cursor for members in the roles
SET @rolememberscur = CURSOR FOR SELECT u.name
from sysusers u, sysusers g, sysmembers m
where g.name = @role_name
and g.uid = m.groupuid
and g.issqlrole = 1
and u.uid = m.memberuid

OPEN @rolememberscur
FETCH NEXT FROM @rolememberscur INTO @member_name
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'exec sp_addrolemember ''' + @role_name + ''', ''' + @member_name + ''''
FETCH NEXT FROM @rolememberscur INTO @member_name
END
CLOSE @rolememberscur
DEALLOCATE @rolememberscur

— PRINT @role_name
FETCH NEXT FROM @rolescur INTO @role_name
END

CLOSE @rolescur
DEALLOCATE @rolescur