July 19, 2005 at 1:37 am
Hi All,
Using SQLDMO, I want to find out the Database roles permission that a user is having. I mean if there is a database say: TEST and the user is "sa1" then when I go to Enterprise manager->SQL Server Group->Local->Security->Logins->sa1 (this is the user).
After Rite clicking on sa1 and click the "Database Access" tab and then selecting the TEST Database, I see that the user has 'public' and 'db_owner" database roles. I want to get this (in this case I need public and db_owner)through programmatically using SQLDMO. If there is a stored procedure too, then please let me know.
Any help\hint is much appreciated.
Thanks In advance !
SK
July 20, 2005 at 12:25 am
I never how this will format in here but is this what you are looking for?
PRINT ''
PRINT ''
PRINT ''
PRINT '+-----------------------------------------------------------------------------+'
PRINT '¦ List of users per role'
PRINT '+-----------------------------------------------------------------------------+'
PRINT ''
-- ===================================================================================
-- ===[ Lists Users by Role ]=========================================================
-- ===================================================================================
--
-- Purpose : Lists Users by Role, simulates sp_helpsrvrolemember
--
-- ===================================================================================
SET NOCOUNT ON
BEGIN
CREATE TABLE #Temp_Roles
(
ServerRole VARCHAR(60) NOT NULL,
MemberName VARCHAR(100) NOT NULL,
MembersSID VARBINARY(100) NOT NULL
)
INSERT #Temp_Roles EXEC master..sp_helpsrvrolemember
SELECT ServerRole AS [Server Role], MemberName AS [Member Name], MembersSID AS [Members SID]
FROM #Temp_Roles
DROP TABLE #Temp_Roles
END
SET NOCOUNT OFF
GO
-- ===================================================================================
-- ===================================================================================
-- ===================================================================================
-- ===[ Lists Users by Security ]=====================================================
-- ===================================================================================
--
-- Purpose : Lists Users by Security, simulates sp_helpsrvrolemember
--
-- ===================================================================================
-- Security Groups
SET NOCOUNT ON
BEGIN
CREATE TABLE #Temp_UserSecurity
(
[Server] SYSNAME,
[Login Name] SYSNAME NULL,
[Login Type] SYSNAME,
[DB Name] SYSNAME,
[Deny Login] VARCHAR(5),
[Has Access] INT,
[is NT Name] INT,
[is NT Group] INT,
[is NT User] INT,
[Sys Admin] INT,
[Security Admin] INT,
[Server Admin] INT,
[Setup Admin] INT,
[Process Admin] INT,
[Disk Admin] INT,
[DB Creator] INT,
[Bulk Admin] INT
)
INSERT #Temp_UserSecurity
SELECT
Server = RTRIM(UPPER(@@SERVERNAME)),
loginname = CONVERT(SYSNAME, name),
dbname = CONVERT(SYSNAME, DB_NAME(dbid)),
loginType = CONVERT(SYSNAME, name),
denylogin = CONVERT(INT, CASE WHEN (xstatus & 1) = 1 THEN 1 ELSE 0 END),
hasaccess = CONVERT(INT, CASE WHEN (xstatus & 2) = 2 THEN 1 ELSE 0 END),
isntname = CONVERT(INT, CASE WHEN (xstatus & 4) = 4 THEN 1 ELSE 0 END),
isntgroup = CONVERT(INT, CASE WHEN (xstatus & 12) = 4 THEN 1 ELSE 0 END),
isntuser = CONVERT(INT, CASE WHEN (xstatus & 12) = 12 THEN 1 ELSE 0 END),
sysadmin = CONVERT(INT, CASE WHEN (xstatus & 16) = 16 THEN 1 ELSE 0 END),
securityadmin = CONVERT(INT, CASE WHEN (xstatus & 32) = 32 THEN 1 ELSE 0 END),
serveradmin = CONVERT(INT, CASE WHEN (xstatus & 64) = 64 THEN 1 ELSE 0 END),
setupadmin = CONVERT(INT, CASE WHEN (xstatus & 128) = 128 THEN 1 ELSE 0 END),
processadmin = CONVERT(INT, CASE WHEN (xstatus & 256) = 256 THEN 1 ELSE 0 END),
diskadmin = CONVERT(INT, CASE WHEN (xstatus & 512) = 512 THEN 1 ELSE 0 END),
dbcreator = CONVERT(INT, CASE WHEN (xstatus & 1024) = 1024 THEN 1 ELSE 0 END),
bulkadmin = CONVERT(INT, CASE WHEN (xstatus & 4096) = 4096 THEN 1 ELSE 0 END)
FROM sysxlogins
WHERE srvid IS NULL
-- ===================================================================================
-- ===[ Display from #uSP_TempTable ]=================================================
SELECT
[Server],
[Login Name],
[DB Name],
[LoginType] = CASE WHEN [is NT Name] = 1 THEN 'NT User or Group Login' ELSE 'SQL Server Login' END,
[Deny Login] = CASE WHEN [Deny Login] = 1 THEN 'Yes' ELSE '-' END,
[Has Access] = CASE WHEN [Has Access] = 1 THEN 'Yes' ELSE '-' END,
[is NT Name] = CASE WHEN [is NT Name] = 1 THEN 'Yes' ELSE '-' END,
[is NT Group] = CASE WHEN [is NT Group] = 1 THEN 'Yes' ELSE '-' END,
[is NT User] = CASE WHEN [is NT User] = 1 THEN 'Yes' ELSE '-' END,
[Sys Admin] = CASE WHEN [Sys Admin] = 1 THEN 'Yes' ELSE '-' END,
[Security Admin] = CASE WHEN [Security Admin] = 1 THEN 'Yes' ELSE '-' END,
[Server Admin] = CASE WHEN [Server Admin] = 1 THEN 'Yes' ELSE '-' END,
[Setup Admin] = CASE WHEN [Setup Admin] = 1 THEN 'Yes' ELSE '-' END,
[Process Admin] = CASE WHEN [Process Admin] = 1 THEN 'Yes' ELSE '-' END,
[Disk Admin] = CASE WHEN [Disk Admin] = 1 THEN 'Yes' ELSE '-' END,
[DB Creator] = CASE WHEN [DB Creator] = 1 THEN 'Yes' ELSE '-' END,
[Bulk Admin] = CASE WHEN [Bulk Admin] = 1 THEN 'Yes' ELSE '-' END
FROM #Temp_UserSecurity
DROP TABLE #Temp_UserSecurity
END
SET NOCOUNT OFF
GO
-- ===================================================================================
-- ===================================================================================
PRINT ''
PRINT ''
PRINT ''
PRINT '+-----------------------------------------------------------------------------+'
PRINT '¦ List of special users per database'
PRINT '+-----------------------------------------------------------------------------+'
PRINT ''
-- ===================================================================================
-- ===[ Lists Users by Security ]=====================================================
-- ===================================================================================
--
-- Purpose : Lists Users by Specialized Security, simulates sp_helpsrvrolemember
--
-- ===================================================================================
SET NOCOUNT ON
DECLARE @name SYSNAME
DECLARE @SQL NVARCHAR(600)
CREATE TABLE #Temp_UserSecurity
(
[DATABASE NAME] SYSNAME NOT NULL,
SYSNAME NOT NULL,
[ROLE NAME] SYSNAME NOT NULL
)
DECLARE c1 CURSOR FOR
SELECT name FROM master.dbo.sysdatabases
OPEN c1
FETCH c1 INTO @name
WHILE @@fetch_status >= 0
BEGIN
SELECT @SQL =
'INSERT INTO #Temp_UserSecurity
SELECT N'''+ @name + ''', a.name, c.name
FROM ' + QuoteName(@name) + '.dbo.sysusers a
JOIN ' + QuoteName(@name) + '.dbo.sysmembers b ON b.memberuid = a.uid
JOIN ' + QuoteName(@name) + '.dbo.sysusers c ON c.uid = b.groupuid
WHERE a.name != ''dbo'''
/* Insert row for each database */
EXEC (@SQL)
FETCH c1 INTO @name
END
CLOSE c1
DEALLOCATE c1
SELECT * FROM #Temp_UserSecurity
DROP TABLE #Temp_UserSecurity
SET NOCOUNT OFF
GO
-- ===================================================================================
-- ===================================================================================
July 21, 2005 at 12:05 am
In DMO I think you need to look at each of the members for each of the roles in the database/server. checkout EnumServerRoleMember in BOL.
Julian Kuiters
juliankuiters.id.au
July 22, 2005 at 9:19 am
Here is a script that uses the IsMember method to find database owners.
// ----------------------------------------------------------------------------
//
// Name: SAReport
//
// Description:
// Generates a report of all the users in the SA Role, as well as the users that
// are dbo with the databases, from all the servers registered in the Enterprise
// Manager.
//
// Inputs:
// none
//
// Outputs:
//
// Usage:
// Open a cmd prompt window and set the current dir
// cd
// CScript SAReport.js
// or include the full path to the file in the execute
// CScript SAReport.js
// To send the output to a file:
// CScript //Nologo SAReport.js >C:\TEMP\SAReport.csv
//
// Author:
// Norman Kelm
//
// Revision History:
// 000 16 Apr 2003 NRK Original Version
//
// ----------------------------------------------------------------------------
var oApplication = new ActiveXObject( "SQLDMO.Application" );
var oRegServer = new ActiveXObject( "SQLDMO.RegisteredServer" );
var oServerGroup = new ActiveXObject( "SQLDMO.ServerGroup" );
var oSS = new ActiveXObject( "SQLDMO.SQLServer" );
var cSQL = new String();
var bOutput;
// Get all the servers that are registered in the enterprise manager
for( var i = 1; i < oApplication.ServerGroups.Count+1; i++ ) {
oServerGroup = oApplication.ServerGroups(i);
if( oServerGroup.Name == "Local" )
continue;
for( var j = 1; j < oServerGroup.RegisteredServers.Count + 1; j++ ) {
oRegServer = oServerGroup.RegisteredServers(j);
iStatus = DMOConnection( oSS , oRegServer.Name , oRegServer.Login , oRegServer.Password );
if( iStatus == 0 ) {
for( var iLogin = 1; iLogin < oSS.Logins.Count + 1; iLogin++ ) {
cOutput = oRegServer.Name
+ " "
+ oSS.Logins( iLogin ).Name;
bOutput = false;
cSA = ", ";
if( oSS.Logins( iLogin ).IsMember( "sysadmin" ) ) {
cSA = ",sa";
bOutput = true;
} // end if sa
cOutput = cOutput
+ cSA
;
// Could not use the Databases collection in the SQL-DMO since there is no
// way to tell if a database is inactive. Therefore the sysdatabases
// table will be queried directly to obtain a list of active databases
cSQL = "SELECT name FROM master..sysdatabases WHERE status & 512 = 0 AND name NOT IN ( 'pubs' , 'Northwind' )";
if( oQRDB = oSS.ExecuteWithResults( cSQL ) ) {
for( var iDatabase = 1; iDatabase < oQRDB.Rows + 1; iDatabase++ ) {
cDatabaseName = oQRDB.GetColumnString( iDatabase , 1 );
for( var iUser = 1; iUser < oSS.Databases( cDatabaseName ).Users.Count + 1; iUser++ ) {
if( oSS.Databases( cDatabaseName ).Users( iUser ).Login == oSS.Logins( iLogin ).Name ) {
var dbo = "";
if( oSS.Databases( cDatabaseName ).Users( iUser ).IsMember( 'db_owner' ) )
dbo = "\\dbo";
cOutput = cOutput + "," + oSS.Databases( cDatabaseName ).Name + dbo ;
bOutput = true;
} // end if dbo
} // end for users
} // end for databases
} // end if query
if( bOutput ) {
WScript.echo( cOutput );
} // end if output
} // end for logins
} // end if status
oSS.Disconnect();
// WScript.echo( oServerGroup.Name + " " + oRegServer.Name + "\nUser Name: " + oRegServer.Login + "\nPassword: " + oRegServer.Password + "\nSA Login: " + oRegServer.SaLogin + "\nTrusted: " + oRegServer.UseTrustedConnection );
} // end for registered servers
} //end for server groups
oSS.Close();
oSS = undefined;
oApplication.Quit();
oRegServer = undefined;
oServerGroup = undefined;
oApplication = undefined;
function DMOConnection( oSS , Server , UserName , Password ) {
//
// DMOConnection
//
// Description:
// Opens a connection to the requested server and database using the aapropriate
// security mode using the SQLDMO connection method.
//
// Inputs:
// oSS, A reference to an SQL-DMO SQLServer object created elsewhere
// Server, The name of the server to connect
// UserName, The user name of the login within the SQL Server
// leave this blank if using NT Authetication
// Password, The passeord for the login within the SQL Server
//
// Outputs:
// none
//
// Author:
// Norman Kelm
//
// Revision History:
// 000 03/06/02 NRK Original Verision
//
try{
if( UserName == "" ) {
oSS.LoginSecure = true;
oSS.Connect( Server );
} else {
oSS.LoginSecure = false;
oSS.Connect( Server , UserName , Password );
}
return( 0 );
} catch( e ) {
// WScript.echo( "Error source: DMOConnection\nError server: " + Server + "\n" + "Error number: " + e.number + "\n" + "Error name: " + e.name + "\n" + "Error message: " + e.message + "\n" + "Error description: " + e.description );
return( -1 );
}
}
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply