How to find permissions granted on user in a database?

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... Continue Reading →

Advertisements

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

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,... Continue Reading →

Create a free website or blog at WordPress.com.

Up ↑