An Oracle Spin by Alex Lima

Sharing Tips & Tricks from my Database Experience

Menu Skip to content
  • Home
  • GoldenGate
  • General DBA
  • Oracle White Papers
  • Disclosure
  • About
November 25, 2009February 9, 2016 Alex Lima

Script to create grants to roles to database for SQL authenticated users.

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
, 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 sl.isntuser = 0
AND sl.name != 'sa'
AND u.uid = m.memberuid
ORDER BY sl.name

DECLARE @l_sql_stmt VARCHAR(200)
DECLARE @l_user VARCHAR(200), @l_prev_user VARCHAR(200)

SET @l_prev_user = ''
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @l_sql_stmt, @l_user
WHILE @@FETCH_STATUS = 0
BEGIN
IF @l_prev_user != @l_user
BEGIN
PRINT 'IF EXISTS (SELECT 1 FROM sysusers WHERE [name] = ''' + @l_user + ''')'
PRINT ' EXEC sp_revokedbaccess ''' + @l_user + ''''
PRINT 'EXEC sp_grantdbaccess ''' + @l_user + ''''
SET @l_prev_user = @l_user
END
PRINT @l_sql_stmt
FETCH NEXT FROM db_cursor INTO @l_sql_stmt, @l_user
END
CLOSE db_cursor
DEALLOCATE db_cursor

Share this:

  • Email
  • Tweet
  • Share on Tumblr
  • Telegram
  • WhatsApp

Like this:

Like Loading...
  • Grant
  • SQL Server
  • .login
  • authenticate
  • create
  • permission
  • script
  • sp_grantdbaccess
  • sql

Published by Alex Lima

View all posts by Alex Lima

Post navigation

Previous How to find permissions granted on individual objects in a database?
Next How to find length of a TEXT column?

You must log in to post a comment.

Follow Me

  • Twitter
  • LinkedIn
  • YouTube

Categories

  • 18c (2)
  • 19c (4)
  • AIX (1)
  • Archivelog (3)
  • Auditing (2)
  • AWR (10)
  • Backup (4)
  • Backup & Recovery (35)
  • Batch (1)
  • Big Data (1)
  • Built-in stored procedures (1)
  • Connections (1)
  • Control file (3)
  • Cursor (3)
  • Database (12)
  • Dataguard (3)
  • Dataguard (4)
  • Datatypes (6)
  • Date (2)
  • Docker (1)
  • exp (2)
  • Export (6)
  • Flashback (5)
  • General DBA (274)
  • Globalization (6)
  • GoldenGate (30)
  • Grant (5)
  • Initialization (4)
  • install (2)
  • Internal (4)
  • JDBC (5)
  • Jobs (4)
  • Linux (1)
  • Listener (4)
  • LOB (2)
  • Lock (3)
  • Log Miner (1)
  • mac (2)
  • Microservices (15)
  • Monitor (1)
  • MySQL (2)
  • NetApp for Oracle (2)
  • OCI (2)
  • OEM – Grid Control (5)
  • OpenWorld 2010 (3)
  • ORA Errors (3)
  • Oracle RAC (4)
  • oradebug (9)
  • OS (2)
  • osql (1)
  • Parameters (8)
  • partition (1)
  • Password (3)
  • performance (1)
  • PL/SQL (5)
  • postgres (1)
  • Power Shell (2)
  • Primavera (3)
  • Profiler (1)
  • Project Management (3)
  • REST API (3)
  • RMAN (36)
  • role (3)
  • Scheduler (9)
  • Scripts (26)
  • Security (7)
  • sga (4)
  • Shared Pool (3)
  • Shell (15)
  • snapshot (1)
  • SQL – DDL (5)
  • SQL Developer (3)
  • SQL Server (69)
  • SQL*Plus (18)
  • SQLcl (2)
  • startup (4)
  • Statistics (14)
  • Statspack (3)
  • Table (3)
  • tablespace (2)
  • trace (32)
  • Transaction (5)
  • Tuning (6)
  • Tuning (35)
  • Undo (1)
  • Unix (33)
  • Upgrade (1)
  • vi (5)
  • windows (9)

Follow Alex's Blog

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 103 other subscribers

Twitter Updates

  • 3 people followed me and one person unfollowed me // automatically checked by fllwrs.com 15 hours ago
  • On fire with my @QuanticSchool Business Foundations courses! quantic.edu #WhoaWereHalfwayThere #QuanticMBA 2 days ago
  • #ChatGPT knows better! đŸ˜‚ https://t.co/v6ZsFO07VK 2 days ago
  • Best Dr. I ever had. If you ever need heart surgery, check him out. Thomas E. MacGillivray Elected President of The… twitter.com/i/web/status/1… 3 days ago
  • I liked this a lot.... twitter.com/thatjeffsmith/… 3 days ago
Follow @aoflima

Recent Posts

  • Oracle GoldenGate Advantages
  • GoldenGate Automation with REST API and Python
  • Where is the TNS_ADMIN in GoldenGate Microservices 21c?
  • GoldenGate Lifetime Support Schedule
  • Oracle MAA Never Down Series

Top Posts

  • How to find jobs currently running or history about the jobs?
  • How to stop a job scheduled in DBMS_SCHEDULER?
  • How to find the NLS_LANG to set for a database?
  • How to see current utilization of processes/sessions and max utilization?
  • How to delete/reset a parameter in spfile using ALTER SYSTEM?
  • ALTER DATABASE BEGIN/END BACKUP
  • How to grant SELECT access to v$session to other users?
  • How to delete archive logs already archived to backup device?
  • How to extend an account whose password is expiring?
  • How to lock/unlock statistics on a table?
Powered by WordPress.com.
Go to mobile version
%d bloggers like this: