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' … Continue reading How to find permissions granted on user in a database?
Code that be used to scripts to extract role permissions for a database for SQL authenticated accounts, it is useful when rebuilding a server and after databases are reattached the SQL authenticated users access has to be granted. DECLARE db_cursor CURSOR FOR SELECT 'exec sp_addrolemember ''' + g.name + ''', ''' + sl.name + '''', sl.name FROM sysusers u , sysusers g , sysmembers m … Continue reading Script to create grants to roles to database for SQL authenticated users.
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 … Continue reading How to find permissions granted on individual objects in a database?
— 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 … Continue reading How to list the access the roles the different users have?
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 ''' … Continue reading How to create SQL scripts to recreate grants on a database granted through a role?
