February 22, 2009 at 3:17 am
Hi everybody
As you know, when we create a Login, we can map it to a specific database user for each database.
For example, suppose we have login TestLogin. This login could be mapped to User1 in Database1 and to User2 in Database2 and so on.
Now, my question is that how can we identify to which database users a login is mapped?
In our example I need to know that the login TestLogin is mapped to User1 in Database1, User2 in Database 2 and so on.
I examined some security catalog views, but I couldn't find any to help me.
I look forward to hear from you
February 22, 2009 at 3:30 am
If you want to check a login, you can run sp_helplogins stored procedure. It will show you (among other things) the mapping users that the login has in all databases. If you want to check a user and see to which login it maps to, you can use sp_helpuser stored procedure.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 22, 2009 at 3:34 am
Wooow
Great
Thanks dude 🙂
February 22, 2009 at 3:45 am
Sorry to ask again
Now kindly let me know that how can I extract the output table provided by sp_helplogins?
For example, I need to print the associated database users with a specific login.
February 22, 2009 at 5:36 am
Sorry, I don’t realy know how to get the second recordset using SQL Script. If no one will be able to offer you a way to do it, you could check the procedure’s code and use the query that populates the second recordset.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 22, 2009 at 11:52 am
Since you're using SQL Server 2005, the catalog views you'll want to use are sys.server_principals and sys.database_principals (in each database). If you were using SQL Server 2000 or prior, you'd use syslogins and sysusers, respectively. The value that ties the login to the user is the SID. So if you know the SID for the login, you can query every database sys.database_principals for the name that corresponds to that SID. If a match is found, the login has access to the database. The exceptions are members of the sysadmin fixed server role, logins with CONTROL SERVER rights, or logins which actually own databases. They won't show up because of implicit access and login to user mappings. But for the normal cases, this script should do it for you:
DECLARE @LoginName sysname;
DECLARE @DatabaseName sysname;
DECLARE @SQL NVARCHAR(4000);
DECLARE @sid VARCHAR(255);
SET @LoginName = '<Login Name You are Searching On>';
SET @sid = (SELECT sid FROM sys.server_principals WHERE [name] = @LoginName);
CREATE TABLE #Mappings (
DatabaseName sysname,
UserName sysname);
DECLARE cursDatabases CURSOR FAST_FORWARD FOR SELECT name FROM sys.databases;
OPEN cursDatabases;
FETCH NEXT FROM cursDatabases INTO @DatabaseName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @SQL = 'INSERT INTO #Mappings (DatabaseName, UserName)
SELECT ''' + @DatabaseName + ''', name
FROM [' + @DatabaseName + '].sys.database_principals
WHERE sid = ''' + @sid + ''';';
EXEC(@SQL);
FETCH NEXT FROM cursDatabases INTO @DatabaseName;
END
CLOSE cursDatabases;
DEALLOCATE cursDatabases;
SELECT * FROM #Mappings;
DROP TABLE #Mappings;
EDIT: Removed the PRINT @SQL; I had for debugging from the WHILE loop.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply