ORDER BY DBLogin_Database, DBLogin_Owner DESC, DBLogin_Name, DBLogin_Type SELECT DBLogin_Database, DBLogin_Name, MAX(DBLogin_Type) AS DBLogin_Type, CAST(MAX(DBLogin_Owner) AS BIT) AS DBLogin_Owner FROM #DBLogin UPDATE #DBLogin SET DBLogin_Type = 'SQL_USER' WHERE (DBLogin_Type IS NULL) AND (DBLogin_Owner = 1) AND (DBLogin_Name NOT LIKE '%_\_%') UPDATE #DBLogin SET DBLogin_Type = 'WINDOWS_USER' WHERE (DBLogin_Type IS NULL) AND (DBLogin_Owner = 1) AND (DBLogin_Name LIKE '%_\_%') UPDATE A SET A.DBLogin_Type = (SELECT TOP(1) B.DBLogin_Type FROM #DBLogin B WHERE B.DBLogin_Name = A.DBLogin_Name AND B.DBLogin_Type IS NOT NULL) FROM #DBLogin A WHERE (A.DBLogin_Type IS NULL) AND (A.DBLogin_Owner = 1) Do some guess-work for the owner's login type (comment out if you prefere to have NULL instead of guess-work) LEFT OUTER JOIN sys.server_principals SP ON SUSER_SNAME(DB.owner_sid) = SP.name SELECT DB.name AS DBLogin_Database, SUSER_SNAME(DB.owner_sid) AS DBLoginName, type_desc AS DBLogin_Type, 1 AS DBLogin_Owner SELECT DB_NAME() AS, name AS DBLogin_Name, type_desc AS DBLogin_Type, 0 AS DBLogin_OwnerįROM sys.database_principals WHERE 1 = 2 ĭECLARE AS NVARCHAR(MAX) = 'USE ? INSERT INTO #DBLogin SELECT ''?'' AS, name AS DBLogin_Name, type_desc AS DBLogin_Type, 0 AS DBLogin_Owner FROM sys.database_principals WHERE type != ''R'' ' IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'.') AND type in (N'U')) It loops through every database and writes the DB's principals into a temporary table ( #DBLogin), appends the owner-information to it, updates the owner's login type if NULL, executes a SELECT on the temporary table and drops the table again, leaving you with a nice tabular result with all logins for each DB (can be executed in a single go). I use the following batch to list all logins of all databases including their owners. LEFT OUTER JOIN sys.database_permissions AS dp ON dp.grantee_principal_id = u.principal_id and dp.type = 'CO' SELECTĪS sysname),'''') + ']' + + quotename(db_name(),'''') + ']' + + quotename(u.name,'''') + ']' AS ,ĬAST(CASE dp.state WHEN N'G' THEN 1 WHEN 'W' THEN 1 ELSE 0 END AS bit) AS And this query is something that somebody at Microsoft has written (you know, the peeps who know the product inside and out, aka, the experts), so they are familiar with all the weird "flags" in the database.īut the SSMS/GUI -> Sql Profiler tricks works in many scenarios. I got the below when I was in MyDatabase / Security / Users and clicked "refresh" on the "Users".Īgain, I didn't come up with the WHERE clause and the LEFT OUTER JOIN, it was a part of the SSMS query. Then right click in the GUI (in SSMS) and click "Refresh". Whenever you 'see' something in the GUI (SSMS) and you're like "that's what I need", you can always run Sql Profiler to fish for the query that was used.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |