October 8, 2008 at 12:50 pm
KDASQL (10/8/2008)
Getting this error when I run this script in sql 2000:
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'Tempdb.sys.objects'.
This is happening because there is no sys.objects in SQLServer 2000
Replace the first IF statement with this, it wil work:
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) = '9'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
Apologise for this over look. I dont have a SQL 2000 instance for testing.
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[/url]
October 8, 2008 at 1:01 pm
Here is the most updated script, With added code to use SysObjects in 2000 instead of sys.objects
/**
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
**/
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) = '8'
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.memberuid = 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[/url]
October 8, 2008 at 3:31 pm
shiva challa (10/8/2008)
Here is the most updated script, With added code to use SysObjects in 2000 instead of sys.objects/**
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
**/
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) = '8'
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.memberuid = 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
After I run the script the UserName and the GroupName are the same.
Shouldn't the GroupName reflect which group the UserName is associated with....if username: xyz has dbo rights to a database shouldn't dbo show in the GroupName.
Please advise.
Thanks
October 9, 2008 at 7:35 am
No idea. I'm not real sure what the groups are supposed to look like. Someone who is actually a 2000 dba will have to look at it. I'm a developer and my database gets restored from a copy from production. Most of the users from the prod database don't have logins on my dev server so my results are mostly garbage.
BTW you can get the default db on SQL2K with:
l.dbname AS Default_db_Name,
On the first go, I just nulled it out because I hadn't looked at it closely enough to find the 2000 equivalent of the 2005 field. I don't think 2000 has schemas.
October 9, 2008 at 7:56 am
Ah ha! The center part of the 2000 join should be:
FROM HISAppt.dbo.sysUsers u
LEFT JOIN (HISAppt.dbo.sysMembers m
JOIN HISAppt.dbo.sysUsers r
ON m.groupuid = r.uid) -- groupuid not memberuid
ON m.memberuid = u.uid
LEFT JOIN dbo.sysLogins l
ON u.sid = l.sid
That fixes the group thing.
October 9, 2008 at 8:30 am
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[/url]
October 29, 2008 at 4:28 pm
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,
Just say No to Facebook!October 30, 2008 at 8:26 am
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[/url]
November 11, 2008 at 10:39 am
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
November 11, 2008 at 12:14 pm
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[/url]
November 11, 2008 at 12:35 pm
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
December 10, 2008 at 7:45 am
Great script. I removed the different tests for versions 8 and 9 and got it running in SQL Server 2008 ! Many thanks.
December 10, 2008 at 2:40 pm
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[/url]
February 11, 2009 at 8:22 am
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.
February 11, 2009 at 8:37 am
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[/url]
Viewing 15 posts - 16 through 30 (of 60 total)
You must be logged in to reply to this topic. Login to reply