Re: Admin users on SQL Server

From: John (john_w_john2001NOSPAMNOSPAM@yahoo.com)
Date: 07/23/02 

From: "John" <john_w_john2001NOSPAMNOSPAM@yahoo.com>
Date: Tue, 23 Jul 2002 13:03:32 +0530

Thanks Barry. Appreciate it.

"Barry McAuslin" <barry_mcauslin@nospam.yahoo> wrote in message
news:OLZqgzYMCHA.1624@tkmsftngp10...
> Try the following script I have written to do just that.
>
> /*
> Script to go through all databases and list all users with permissions
> in that database.
> Written by Barry McAuslin
> */
> USE master
> GO
>
> DECLARE @dbname SYSNAME
>
> SET NOCOUNT ON
>
> CREATE TABLE #logins
> (
> dbname SYSNAME NOT NULL,
> sid VARBINARY(85) NOT NULL,
> name SYSNAME NOT NULL,
> isdbo SMALLINT NOT NULL
> )
>
> DECLARE dbcsr INSENSITIVE CURSOR FOR
> SELECT name FROM dbo.sysdatabases
> WHERE status & 33760 = 0
> FOR READ ONLY
>
> OPEN dbcsr
>
> FETCH NEXT FROM dbcsr INTO @dbname
>
> WHILE @@FETCH_STATUS = 0
> BEGIN
>
> EXEC ('USE ' + @dbname + '
>
> DECLARE @dborole SMALLINT
>
> SELECT @dborole = uid
> FROM dbo.sysusers
> WHERE name = N''db_owner'' AND status = 0
>
> INSERT INTO #logins (dbname, sid, name, isdbo)
> SELECT DB_NAME(), sid, name, ISNULL(groupuid, 0)
> FROM sysusers AS su LEFT JOIN (SELECT memberuid, groupuid FROM sysmembers
> WHERE groupuid = @dborole) AS sm
> ON su.uid = sm.memberuid
> WHERE sid IS NOT NULL')
>
> FETCH NEXT FROM dbcsr INTO @dbname
>
> END
>
> CLOSE dbcsr
>
> DEALLOCATE dbcsr
>
> SET NOCOUNT OFF
>
> SELECT sl.loginname,
> ISNULL(l.dbname, N'<None>') as dbname,
> ISNULL(l.name, N'<None>') AS name,
> CASE l.isdbo WHEN 0 THEN 'No' ELSE 'Yes' END AS dbo
> FROM master.dbo.syslogins AS sl LEFT JOIN #logins AS l
> ON sl.sid = l.sid
> WHERE IS_SRVROLEMEMBER ( N'sysadmin', sl.loginname) = 0
> UNION ALL
> SELECT loginname, N'<All>', N'<N/A>', N'Yes'
> FROM master.dbo.syslogins
> WHERE IS_SRVROLEMEMBER ( N'sysadmin', loginname) = 1
> ORDER BY sl.loginname, l.dbname
>
>
> DROP TABLE #logins
>
>
> "John" <john_w_john2001NOSPAMNOSPAM@yahoo.com> wrote in message
> news:#qe16mYMCHA.2168@tkmsftngp09...
> > Using TSQL, how can I find out the number of users with admin privilages
> > (including those with NT accounts)on a given SQL Server?
> >
> > Thx
> >
> >
> >
> >
> >
>
>

Relevant Pages
Quantcast