Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
thanksfor help
Posting Yak Master
106 Posts |
 Posted - 2007-01-29 : 17:24:01
|
| Hi,I like to find all the user logins that have db_owner role in any database in a particular sql server.Thanks in advance |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-01-29 : 17:28:16
|
| In Enterprise Manager, select a database, select the Roles in the database, right-click, and select Properties.CODO ERGO SUM |
 |
|
|
thanksfor help
Posting Yak Master
106 Posts |
Posted - 2007-01-29 : 17:33:49
|
| thanks for quick answer.But I would like to know all the user for that sql server how have db_owner role to different DB. I would like to know a query for this on system tables.thanks |
|
|
|
MohammedU
Posting Yak Master
145 Posts |
Posted - 2007-01-30 : 01:09:46
|
| 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 http://groups.google.com/group/microsoft.public.sqlserver.security/browse_thread/thread/722c1071ec858629/d2459ca1cc72baf2?lnk=st&q=script+to+list+all+dbo+users+&rnum=5#d2459ca1cc72baf2MohammedU |
|
|
|
|
|
|
|
|
find all the db_owner