Existence of Login

I am using the following command to give the NT Group CustomAdmin access to
a database.
use MyDatabase
EXEC sp_grantdbaccess 'MyServer\CustomAdmin'
Is there an IF statement I can use to test if MyServer\CustomAdmin before
granting the rights?
Derek Hartif not exists (select * From master..syslogins where name =
'MyServer\CustomAdmin')
print 'do something'
Keith Kratochvil
"Derek Hart" <derekmhart@.yahoo.com> wrote in message
news:O$wjkFIcGHA.1208@.TK2MSFTNGP02.phx.gbl...
>I am using the following command to give the NT Group CustomAdmin access to
>a database.
> use MyDatabase
> EXEC sp_grantdbaccess 'MyServer\CustomAdmin'
> Is there an IF statement I can use to test if MyServer\CustomAdmin before
> granting the rights?
> Derek Hart
>|||Derek Hart (derekmhart@.yahoo.com) writes:
> I am using the following command to give the NT Group CustomAdmin access
> to a database.
> use MyDatabase
> EXEC sp_grantdbaccess 'MyServer\CustomAdmin'
> Is there an IF statement I can use to test if MyServer\CustomAdmin before
> granting the rights?
Which version of SQL Server?
(The security stuff is so different that I don't want to suggest a
crappy SQL 2000 solution on SQL 2005.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Xref: TK2MSFTNGP01.phx.gbl microsoft.public.sqlserver.programming:601409
Keith Kratochvil (sqlguy.back2u@.comcast.net) writes:
> if not exists (select * From master..syslogins where name =
> 'MyServer\CustomAdmin')
> print 'do something'
But that would be for server access, wouldn't it? Derek was asking for
database access.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||SQL2000
I want to check if the server (Windows NT or Windows 2003) has that as a
group. I believe nothing would yet be in SQL Server. Before I add logins
to SQL Server and grant database access to logins, I want to verify that
these logins first exist on the server. Is there a way. I don't know much
about Active Directory or how to access it.
Derek Hart
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BB3CA34A3AYazorman@.127.0.0.1...
> Derek Hart (derekmhart@.yahoo.com) writes:
> Which version of SQL Server?
> (The security stuff is so different that I don't want to suggest a
> crappy SQL 2000 solution on SQL 2005.)
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||Derek Hart (derekmhart@.yahoo.com) writes:
> I want to check if the server (Windows NT or Windows 2003) has that as a
> group. I believe nothing would yet be in SQL Server. Before I add
> logins to SQL Server and grant database access to logins, I want to
> verify that these logins first exist on the server. Is there a way. I
> don't know much about Active Directory or how to access it.
Nor do I! Thankfully, that should not be necssary.
To check on server level, to as Keith posted:
if not exists (select * From master..syslogins where name =
'MyServer\CustomAdmin')
exec sp_grantlogin 'MyServer\CustomAdmin')
On database level do:
if not exists(select * from
db..sysusers where name = 'MyServer\CustomAdmin')
exec db..sp_grantdbaccess 'MyServer\CustomAdmin')
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Correct. My apologies for the providing an incomplete solution!
Keith Kratochvil
"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns97BB61BD26E9Yazorman@.127.0.0.1...
> Keith Kratochvil (sqlguy.back2u@.comcast.net) writes:
> But that would be for server access, wouldn't it? Derek was asking for
> database access.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx