How to find user existence
Posted on 2003-12-10
i got a problem like i need to write create datebase script,and after creation i need to add login and also add user to that
Sp aftre creation of database i am writing following script.
DECLARE @SQL_LOGIN VARCHAR(30)
DECLARE @PASSWORD VARCHAR(15)
DECLARE @DEFDB VARCHAR(10)
SET @SQL_LOGIN = 'GASPER'
SET @PASSWORD = NULL
if exists(select * from sysusers where name = @SQL_LOGIN)
exec sp_dropuser @SQL_LOGIN
if exists(select * from syslogins where name= @SQL_LOGIN)
exec sp_droplogin @SQL_LOGIN
exec sp_addlogin @SQL_LOGIN,@PASSWORD,@DEFDB
exec sp_adduser @SQL_LOGIN,@SQL_LOGIN
But here the problem is.First i created one database and run the above script, but again i want to create a database
with different name, regarding below condition it need to drop the login and create new login.As that login is dbo
for first database it cannot be dropped.
So i have to write logic like,if login exists i need to check whether that login is dbo for any other database or not.
If yes i have to choose new login else drop login and create with same name.
But i am not getting how to write logic for checking login is dbo for any other database or not.
If you don't understand the question pls ask me again.
Waiting for kind response.