Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»»

List all Usernames, Roles for all the databases. Expand / Collapse
Author
Message
Posted Thursday, October 09, 2008 8:30 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 01, 2013 7:40 AM
Points: 145, Visits: 336
Cathy, thanks for working on it.

here is the updated script:
/**
Script: list all Usernames, Roles for all the databases.
Author: Shiva Challa (http://challa.info)
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.

Directions of Use:
For All Users list: You can directly run this script in SQL Server Management studio
For a specific user:
        1. Find this code and u.name like ''tester''
        2. Uncomment the code
        3. Replace the Name ''tester'' with the username you want to search on.

Resultset:
        DBName: Database name that the user exists in.
        Name: user name.
        GroupName: Group/Database Role that the user is a part of.
        LoginName: Actual login name, if this is null, Name is used to connect.
        default_database_name
        default_schema_name    
        principal_id
        sid

Change History:
08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.
09/02/2008 Cathy Greenselder - Convert to SQL2000
(default_database_name not in SQL2K)
(default_schema_name not in SQL2K)
(principal_id not in SQL2K)
(uid is in SQL2K
10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.
- Added code to use SysObjects in 2000 instead of sys.objects
10/09/2008 Cathy Greenselder - for 2000: switched the SysUsers join to use "m.groupuid" instead of "m.memberuid"
**/

USE MASTER
GO

BEGIN

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser

CREATE TABLE #tuser (
DBName VARCHAR(50),
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name VARCHAR(50) NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85))

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
''?'' as DBName,
u.name As UserName,
CASE
WHEN (r.uid IS NULL) THEN ''public''
ELSE r.name
END AS GroupName,
l.name AS LoginName,
NULL AS Default_db_Name,
NULL as default_Schema_name,
u.uid,
u.sid
FROM ?.dbo.sysUsers u
LEFT JOIN (?.dbo.sysMembers m
JOIN ?.dbo.sysUsers r
ON m.groupuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
/*and u.name like ''tester''*/
ORDER BY u.name
'

ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb '
SELECT
''?'',
u.name,
CASE
WHEN (r.principal_id IS NULL) THEN ''public''
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM ?.sys.database_principals u
LEFT JOIN (?.sys.database_role_members m
JOIN ?.sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN ?.sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R''
/*and u.name like ''tester''*/
order by u.name
'

SELECT *
FROM #TUser
ORDER BY DBName,
[name],
GroupName

DROP TABLE #TUser
END



Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
Post #583355
Posted Wednesday, October 29, 2008 4:28 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 6:12 PM
Points: 886, Visits: 1,528
I must be the only one who still can't get this to work properly. I am using SQL 2005 so the issues specific to SQL 2000 don't apply. I've removed all parts that are for SQL Server 2000 and removed the "IF THEN" items so that the only parts left are the call to sp_MSForEachDB and the INSERT done via the SELECT and while the thing does run, after going through a few DB's on the server it eventually retruns the erro message:

Msg 102, Level 15, State 1, Line 10
Incorrect syntax near '.'.

I can't seem to get it to not return this error. I even ran the thing manually on each DB, removing the use of sp_MSForEachDB to try and determine which DB is the culprit but when I do that I no longer get any errors.

GO figure?

Anyone else have a similiar problem?


Kindest Regards,

A Democracy works great until the day you find yourself on the sheep side of a vote between 5 wolves and 4 sheep on what’s for dinner when neither have eaten in many days. A free Republic where the rights of the few and the individual are protected is the only one in which Freedom and Prosperity for all have a chance to blossom.
Post #594000
Posted Thursday, October 30, 2008 8:26 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 01, 2013 7:40 AM
Points: 145, Visits: 336
Can you post the script that you've got after removing the 2000 part out? I tried doing the same and it worked with out any problem.

try doing it like this (All i am doing here is enclosing the 3 part names in square parentheses):
          FROM [?].[sys].[database_principals] u
LEFT JOIN ([?].[sys].[database_role_members] m
JOIN [?].[sys].[database_principals] r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN [?].[sys].[server_principals] l
ON u.sid = l.sid

Replace the code in the "from"clause with this.
See if you have any spaces in the name of the database.


Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
Post #594372
Posted Tuesday, November 11, 2008 10:39 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:22 PM
Points: 806, Visits: 2,096
This script is a lifesaver. It keeps our Loss Prevention folks happy when they need to audit any servers.

Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #600818
Posted Tuesday, November 11, 2008 12:14 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 01, 2013 7:40 AM
Points: 145, Visits: 336
I am just glad that it is useful :)

Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
Post #600874
Posted Tuesday, November 11, 2008 12:35 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Wednesday, April 09, 2014 12:22 PM
Points: 806, Visits: 2,096
I added a couple more features as well, to extend to object level permissions as well and check for orphaned logins. It's based of the main table your script creates when it derives the new tables.

--First section, slightly tweaked from original script in posting
/**
Script: list all Usernames, Roles for all the databases.
Author: Shiva Challa (http://challa.info)
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.

Directions of Use:
For All Users list: You can directly run this script in SQL Server Management studio
For a specific user:
1. Find this code and u.name like ''tester''
2. Uncomment the code
3. Replace the Name ''tester'' with the username you want to search on.

Resultset:
DBName: Database name that the user exists in.
Name: user name.
GroupName: Group/Database Role that the user is a part of.
LoginName: Actual login name, if this is null, Name is used to connect.
default_database_name
default_schema_name
principal_id
sid

Change History:
08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.
09/02/2008 Cathy Greenselder - Convert to SQL2000
(default_database_name not in SQL2K)
(default_schema_name not in SQL2K)
(principal_id not in SQL2K)
(uid is in SQL2K
10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.
- Added code to use SysObjects in 2000 instead of sys.objects
10/09/2008 Cathy Greenselder - for 2000, switched the SysUsers join to use "m.groupuid" instead of "m.memberuid"
**/

USE MASTER
GO

BEGIN

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser

CREATE TABLE #tuser (
rownum int identity(1,1),
DBName VARCHAR(50),
[Name] varchar(128),
GroupName varchar(128) NULL,
LoginName varchar(128) NULL,
default_database_name VARCHAR(50) NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85))

IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'use ?;
SELECT
db_name(),
u.name As UserName,
CASE
WHEN (r.uid IS NULL) THEN ''public''
ELSE r.name
END AS GroupName,
l.name AS LoginName,
NULL AS Default_db_Name,
NULL as default_Schema_name,
u.uid,
u.sid
FROM .dbo.sysUsers u
LEFT JOIN ( .dbo.sysMembers m
JOIN .dbo.sysUsers r
ON m.groupuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN master.dbo.syslogins l
ON u.sid = l.sid
WHERE (u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1) and u.name not in(''dbo'', ''guest'', ''INFORMATION_SCHEMA'')
/*and u.name like ''tester''*/
ORDER BY u.name
'


ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb 'use ?;
SELECT
db_name(),
u.name,
CASE
WHEN (r.principal_id IS NULL) THEN ''public''
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM .sys.database_principals u
LEFT JOIN ( .sys.database_role_members m
JOIN .sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN .sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R'' and u.name not in (''dbo'', ''guest'', ''sys'', ''INFORMATION_SCHEMA'')
/*and u.name like ''tester''*/
order by u.name
'

SELECT Name "User", dbname, GroupName, LoginName
FROM #TUser
order by Name

--DROP TABLE #TUser -- not dropping now as needed for next two sections.
END
go

------------------------------------
-- NEW SECTIONS
------------------------------------

-- This second section generates all object level permissions

create table #Tobjectlevel
(
dbname varchar(128),
owner varchar(128),
object varchar(128),
grantee varchar(128),
grantor varchar(128),
protecttype varchar(128),
action varchar(128),
[column] varchar(512)
)

exec sp_msforeachdb 'use ?;
insert into #TobjectLevel(owner, object, grantee, grantor, protecttype, action, [column])
exec sp_helprotect
update #TobjectLevel
set dbname = db_name() where dbname is null'


select grantee [User], dbname, object, action [Permissions] from #Tobjectlevel
where grantee in (select Name from #TUser)
order by upper(grantee) asc

go

-- This section creates a list of orphaned users (i.e. those not in #TUser)
create table #TOrphans
(
[User] sysname,
dbname sysname
)

exec sp_msforeachdb 'use ?;
insert into #TOrphans
select name, db_name() from sysusers
where name not in (select name from #TUser)
and name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'')
and roles <> 0x00
'
select * from #TOrphans

go
drop table #TOrphans
drop table #Tobjectlevel
drop table #TUser



Gaby
________________________________________________________________
"In theory, theory and practice are the same. In practice, they are not."
- Albert Einstein
Post #600879
Posted Wednesday, December 10, 2008 7:45 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 25, 2014 1:31 AM
Points: 1, Visits: 150
Great script. I removed the different tests for versions 8 and 9 and got it running in SQL Server 2008 ! Many thanks.
Post #617031
Posted Wednesday, December 10, 2008 2:40 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 01, 2013 7:40 AM
Points: 145, Visits: 336
Very Nice!! it works on 2008 as well. Good to know.
Thanks for the info.


Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
Post #617414
Posted Wednesday, February 11, 2009 8:22 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 7:29 AM
Points: 49, Visits: 134
I like this script and find it useful for generating audit reports; however there are a few enhancements I would like to propose:

1. Any SQL Server object with a hyphen will cause the script to fail, so the script should be altered to use square brackets [] to enclose any potential SQL Server objects with hyphens. For example, a SharePoint database could be named SharePoint_AdminContent_2cb093ab-dbf9-4777-85d1-a867bfd62484. The hyphens in the DB name cause the script to fail unless it is referred to as [SharePoint_AdminContent_2cb093ab-dbf9-4777-85d1-a867bfd62484].

Alter the script as follows anywhere you see the DBName:

2005
FROM [?].sys.database_principals u
LEFT JOIN ([?].sys.database_role_members m
etc.

2000
FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
etc.

2. Make the DBName column larger than varchar(50) and LEFT function the DBName in the select statement with the amount of characters you created the table with so your insert is guaranteed to work. As it stands right now if you have a DNName larger than 50 characters the script will fail


Alter the script as follows:

--Create the temp table with a larger DBName column
CREATE TABLE #tuser (
DBName VARCHAR(100),

--******************************************************
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
LEFT(''?'', 100) as DBName,


3. Add server name to the output. If you are running this for auditing purposes and you run this on several machines, you'd want the server name in the output for when you put all the report together.


Alter the script as follows:

CREATE TABLE #tuser (
ServerName VARCHAR(100),
DBName VARCHAR(100),
--******************************************************
INSERT INTO #TUser
EXEC sp_MSForEachdb
'
SELECT
LEFT(@@SERVERNAME, 100) as ServerName,
LEFT(''?'', 100) as DBName,

FROM [?].dbo.sysUsers u
LEFT JOIN ([?].dbo.sysMembers m
etc.



Best Regards,

Matt Karp

Post #654896
Posted Wednesday, February 11, 2009 8:37 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, November 01, 2013 7:40 AM
Points: 145, Visits: 336
Hi Matt.
I glad that the script was useful. And Thanks for the suggestions.
The changes you suggested are published.


Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help
Post #654917
« Prev Topic | Next Topic »

Add to briefcase «««12345»»»

Permissions Expand / Collapse