Showing posts with label login. Show all posts
Showing posts with label login. Show all posts

Monday, March 12, 2012

expire a user account/login in sql server2000

Quote:

Originally Posted by mmccarthy

This is the Articles section and not the forum. You will find a list of forums under the Forums tab.

If you have an article on topic that you wish to share with the community you can post it here.

ADMIN


how can expire a user account/login in sql server2000You probably want either sp_droplogin or sp_denylogin.

Friday, February 24, 2012

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

Wednesday, February 15, 2012

Execution of script at login

Is there a way to execute a script at login to check to do some
additional security checks after authentication?

Thanks,
James BrakeNot automatically. Why not call the script in the client code that
opens the connection?

Could you explain what additional checks you would require at
connection time. If the user isn't authorized then why would give them
a login to the database?

--
David Portas
SQL Server MVP
--|||Thanks for your reply.

If I had access to client code, I'd probably use an application role.
But, I do not.

The application gives each user read/write access to all tables
regardless of application security.

I was thinking of checking at login time whether the accessed the
database from the application or not.

James|||Hi

If you write anything such as a audit record when they log in, you could use
a trigger to raiserror. It would not be a very elegant solution though!

John

"James" <jbrake@.aeci.org> wrote in message
news:1103312862.596986.208700@.z14g2000cwz.googlegr oups.com...
> Thanks for your reply.
> If I had access to client code, I'd probably use an application role.
> But, I do not.
> The application gives each user read/write access to all tables
> regardless of application security.
> I was thinking of checking at login time whether the accessed the
> database from the application or not.
> James|||James (jbrake@.aeci.org) writes:
> If I had access to client code, I'd probably use an application role.
> But, I do not.
> The application gives each user read/write access to all tables
> regardless of application security.
> I was thinking of checking at login time whether the accessed the
> database from the application or not.

This may be a case of just poor choice of words from your side, but permit
be to point out that the application cannot really give access to the
tables, unless it is logging in with a user that has such privileges.
But maybe you mean that the application requires the user to have
read/write access to the tables, because it is not using stored procedures?

In such case it may be a difficult task to handle. To prevent updates
you could add triggers on the tables that checks app_name() and rolls
back if the application is not the right one.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp