Logins without User Mapping

Hi All
In SQL Server 2005 how do I go about writing a script to determine all
server logins that do not have any User Mapping?
As I have a test server where logins have been created and as databases have
been dropped there are now logins that exist that no longer have access to
any databases and I would like to remove them.
Thanks
Hello,
Take a loook into sp_change_users_logins system proc in books online..
Thanks
Hari
"David" <David@.discussions.microsoft.com> wrote in message
news:67EDE855-7967-4458-8526-1A80E4939578@.microsoft.com...
> Hi All
> In SQL Server 2005 how do I go about writing a script to determine all
> server logins that do not have any User Mapping?
> As I have a test server where logins have been created and as databases
> have
> been dropped there are now logins that exist that no longer have access to
> any databases and I would like to remove them.
> Thanks
|||Hi David
One way to do it is trying to run the query against the syslogins table in
the master database. An example of the query:
SELECT * FROM master.dbo.syslogins WHERE dbname IS NULL.
or try this one:
SELECT *
FROM master.dbo.syslogins
WHERE [sid] NOT IN (SELECT [sid] FROM database1.dbo.sysusers WHERE [sid] IS
NOT NULL)
AND [sid] NOT IN (SELECT [sid] FROM database2.dbo.sysusers WHERE [sid] IS
NOT NULL)
AND dbname <> 'master'
However, you might want to review the result first before deleting them. I
have not really tested the script in details. But logically it should work.
Hopes that's helpful.
Thank you
Lucas
"David" <David@.discussions.microsoft.com> wrote in message
news:67EDE855-7967-4458-8526-1A80E4939578@.microsoft.com...
> Hi All
> In SQL Server 2005 how do I go about writing a script to determine all
> server logins that do not have any User Mapping?
> As I have a test server where logins have been created and as databases
> have
> been dropped there are now logins that exist that no longer have access to
> any databases and I would like to remove them.
> Thanks

Logins without User Mapping

Hi All
In SQL Server 2005 how do I go about writing a script to determine all
server logins that do not have any User Mapping?
As I have a test server where logins have been created and as databases have
been dropped there are now logins that exist that no longer have access to
any databases and I would like to remove them.
ThanksHello,
Take a loook into sp_change_users_logins system proc in books online..
Thanks
Hari
"David" <David@.discussions.microsoft.com> wrote in message
news:67EDE855-7967-4458-8526-1A80E4939578@.microsoft.com...
> Hi All
> In SQL Server 2005 how do I go about writing a script to determine all
> server logins that do not have any User Mapping?
> As I have a test server where logins have been created and as databases
> have
> been dropped there are now logins that exist that no longer have access to
> any databases and I would like to remove them.
> Thanks|||Hi David
One way to do it is trying to run the query against the syslogins table in
the master database. An example of the query:
SELECT * FROM master.dbo.syslogins WHERE dbname IS NULL.
or try this one:
SELECT *
FROM master.dbo.syslogins
WHERE [sid] NOT IN (SELECT [sid] FROM database1.dbo.sysusers WHERE &
#91;sid] IS
NOT NULL)
AND [sid] NOT IN (SELECT [sid] FROM database2.dbo.sysusers WHERE
1;sid] IS
NOT NULL)
AND dbname <> 'master'
However, you might want to review the result first before deleting them. I
have not really tested the script in details. But logically it should work.
Hopes that's helpful.
Thank you
Lucas
"David" <David@.discussions.microsoft.com> wrote in message
news:67EDE855-7967-4458-8526-1A80E4939578@.microsoft.com...
> Hi All
> In SQL Server 2005 how do I go about writing a script to determine all
> server logins that do not have any User Mapping?
> As I have a test server where logins have been created and as databases
> have
> been dropped there are now logins that exist that no longer have access to
> any databases and I would like to remove them.
> Thanks