0

How can I check if a SQL Login exists for an arbitrary domain user if the account I'm running doesn't have SA rights to the instance (but will be dbo on at least one DB)

Ideally I'd like the solution to be automate-able for inclusion into a utility at some point in the future.

If I create a user in my DB and attempt to map it to a SQL Login that doesn't exists will it report that it doesn't exist or just give me a permissions error?

1

SQL Server will allow you to create a database user for a domain account, even if the corresponding login does not exist. No error will be returned as long as the user creating the database user has CREATE USER permissions and the domain account is valid.

However, the new user will not be able to authenticate to the instance until a login for that account (or group the account is a member of) is created.

  • Understood, thanks. Any ideas how I can query if the login exists with only dbo of a DB within the instance? – Kelewan Aug 10 at 18:53
  • @Kelewan, do you mean a login who is the database owner but not a database user? Run this query in each database. A row will be returned if the login is a database user but not the database owner: SELECT sp.name AS LoginName FROM sys.server_principals AS sp JOIN sys.database_principals AS dp ON dp.sid = sp.sid WHERE sp.sid = SUSER_SID(N'YourDomain\YourUser') AND dp.name <> N'dbo'; – Dan Guzman Aug 10 at 20:26
0

If you want to see SQL Instance Logins without being SA, I think you should have securityadmin role. In addition it also necessary for mapping a login to db user.

0

How can I check if a SQL Login exists for an arbitrary domain user if the account I'm running doesn't have SA rights to the instance (but will be dbo on at least one DB)

Hope following script help you:

Declare @Login nvarchar (100) = 'LoginName';

Declare @Temp Table (sid varbinary(85), name nvarchar (100), RoleName varchar (100),DBName nvarchar(256) );

insert into @Temp (sid, name, RoleName, DBName)

exec sp_MSforeachdb
'Use [?];
select dp.sid, dp.name as UserName, RoleName.name as RoleName, DB_Name()
from sys.database_principals as dp
    join sys.database_role_members DPR on dp.principal_id = DPR.member_principal_id
    join sys.database_principals RoleName on DPR.role_principal_id = RoleName.principal_id
where dp.type in(''U'', ''S'')'


select  sp.name as LoginName, 
        RoleName.name as SrvRoleName,
        dp.DBName,
        dp.name as UserName,
        dp.RoleName as DBRoleName
from sys.server_principals as sp
    left join sys.server_role_members SPR on sp.principal_id = SPR.member_principal_id
    left join sys.server_principals RoleName on SPR.role_principal_id = RoleName.principal_id
    left outer join @Temp as dp on sp.sid = dp.sid
where SP.type in('U', 'S') and sp.name = @Login
go

To find users from all user databases that are not mapped to any login:

exec sp_MSforeachdb
'Use [?];
select  DB_NAME () as DBName, dp.name, 
        dp.principal_id, 
        dp.type, 
        dp.type_desc, 
        dp.sid, 
        sp.sid,
        ''use '' + cast(DB_NAME () as varchar(50)) + ''; if not exists (select containment from sys.databases where not containment = 0) drop user ['' + dp.name + '']'' as FixCommand
from sys.database_principals as dp
        left outer join sys.server_principals as sp on dp.sid = sp.sid
where  dp.principal_id > 4 AND sp.sid is null and dp.type in (''S'', ''U'', ''G'')'
go

P.S: Please note that the information from Offline databases could not be retried.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.