SQLGear

Following is the query to list all the orphan users in a SQL Server database:

When you are migrating the all the databases on a server to another then you might have used sp_help_revlogin to script out the logins. That was easy! But if your SQL instance from where you are moving the databases is holding databases for multiple application and application owner do not agree on migrating databases on same date. Or say you want to move different databases to different target servers. In this case you need to script out logins for particular database only. You can use following query, after setting the results to text, to get login creation script for logins associated with perticular database only. Step 1. Setting the results to text: Step 2. Execute the following query in the database for which logins are to be scripted: USE DatabaseName; GO SELECT 'EXEC [dbo].[sp_help_revlogin] @login_name = [' + l.NAME + ']' FROM sys.sysusers u INNER JOIN sys.syslogins l ON l.sid = u.sid You will get the output as follows: Step 3. Copy the outpu...

Credits: David Fulmer Getting access to a production database should have a process where security team should also involved. But someone might give access to a user bypassing the process. If this sort of unauthorized users are found having access to the database during your audit reports you surly want to know about who and when. If a user was created on the database wrongly, and you want to know who created it then following script will help: It will give you HostName from where "Create User ..." was executed and LoginName of the user who executed it and Time when the user was created. USE [DatabaseName]; GO DECLARE @trace_id INT ,@filename NVARCHAR(4000); SELECT @trace_id = id FROM sys.traces WHERE is_default = 1; SELECT @filename = CONVERT(NVARCHAR(4000), value) FROM sys.fn_trace_getinfo(@trace_id) WHERE property = 2; SELECT HostName ,LoginName ,StartTime FROM sys.fn_trace_gettable(@filename, DEFAULT) WHERE EventClass = 109 AND DatabaseName = N'Databas...

Credits Greyhood Ever wanted to check if a database user or custom database role belongs to what other database roles or has what access to the database. Following is the query: DECLARE @Rolename CHAR(15) SET @Rolename = 'Custom DB Role' --//Set the variable value in above line to the database role or user for which membership needs to be checked SELECT @@ServerName [Server] ,DB_NAME() AS [Database] ,@Rolename [DBRole/DBRole] ,CASE IS_ROLEMEMBER(NAME, @Rolename) WHEN 1 THEN 'Is member of ' + NAME WHEN 0 THEN 'Is not member of ' + NAME END AS [Membership] FROM sysUsers WHERE NAME @Rolename AND issqlrole = 1 AND IS_ROLEMEMBER(NAME, @Rolename) = 1 --//comment the above line if you also want to see what the database role (or user) is not member of