Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
Woody
Chris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
'''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>
|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)[vbcol=seagreen]
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
to
>

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
WoodyChris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
'''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=
> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
to[vbcol=seagreen]
>

Add a user via a script

Hello All, I've got a script that will Create a Database and install the
tables onto a SQL server.
what code is used to add a user/username and password to allow the user to
login?
I am aware that I can do this from the SQL Server, However, I've been asked
to do it in the code.
I don't want to add a user to the network - just into 1 database. I think
this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
I've looked at CreateUser. However when I try to run this I get an error
saying, "Could not find stored procedure 'createuser'"
Please help
TIA
WoodyChris
--Creating logins and granting them access to database
SET @.login='bbbbbbbbbbbbbb'
SET @.pass='aaaaaaaaaaaaaaaa'
SET @.db=@.name
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname=@.login)
BEGIN
EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
END
SET @.sql_db='USE '+@.name
SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name='''+@.login+''')')
SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
"Chris" <a@.b.com> wrote in message
news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> Hello All, I've got a script that will Create a Database and install the
> tables onto a SQL server.
> what code is used to add a user/username and password to allow the user to
> login?
> I am aware that I can do this from the SQL Server, However, I've been
> asked
> to do it in the code.
> I don't want to add a user to the network - just into 1 database. I
> think
> this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> I've looked at CreateUser. However when I try to run this I get an error
> saying, "Could not find stored procedure 'createuser'"
>
> Please help
> TIA
> Woody
>|||Hi Uri, thanks for the reply.
I'm having a little trouble running it though.
SET @.login='bbbbbbbbbbbbbb'
Server: Msg 137, Level 15, State 1, Line 2
Must declare the variable '@.login'.
please advise.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uuYHr9nIGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Chris
> --Creating logins and granting them access to database
> SET @.login='bbbbbbbbbbbbbb'
> SET @.pass='aaaaaaaaaaaaaaaa'
> SET @.db=@.name
> IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE
loginname=@.login)
> BEGIN
> EXEC sp_addlogin @.loginame = @.login, @.passwd =@.pass, @.defdb = @.db
> END
> SET @.sql_db='USE '+@.name
> SET @.sql1=(' IF NOT EXISTS (SELECT * FROM sysusers WHERE name=> '''+@.login+''')')
> SET @.sql2=(' BEGIN EXEC sp_grantdbaccess '''+@.login+'''')
> SET @.sql3=(' EXEC sp_addrolemember ''db_owner'','''+@.login+''' END')
> EXEC (@.sql_db+@.sql1+@.sql2+@.sql3)
>
>
>
>
> "Chris" <a@.b.com> wrote in message
> news:eMEQW7nIGHA.3036@.tk2msftngp13.phx.gbl...
> >
> > Hello All, I've got a script that will Create a Database and install the
> > tables onto a SQL server.
> > what code is used to add a user/username and password to allow the user
to
> > login?
> > I am aware that I can do this from the SQL Server, However, I've been
> > asked
> > to do it in the code.
> > I don't want to add a user to the network - just into 1 database. I
> > think
> > this eliminates that code ( sp_grantdbaccess & sp_grantlogin)
> >
> > I've looked at CreateUser. However when I try to run this I get an error
> > saying, "Could not find stored procedure 'createuser'"
> >
> >
> > Please help
> > TIA
> > Woody
> >
> >
>

Activity Captured by profiler

Hi
Does the profiler catch every activity on the server. I mean what about
the various username & password screens , dont you think then its a
threat to security .
& I wanted to knw how to get the last 10 SQL Statments issued by the
users client to the SQL Server.
dbcc inputbuffer (spid) gives us the last statement how to get a
history of statments by a SPID.
Thanks
Hi
Profiler will not display sensitive data such as passwords etc. If you are
worried about passwords and usernames you should also use protocol encryption
between client and server.
You can use a filter in profiler to restict information display to a given
SPID or use the search options to jump between entries for a given SPID.
John
"Double_B" wrote:

> Hi
> Does the profiler catch every activity on the server. I mean what about
> the various username & password screens , dont you think then its a
> threat to security .
> & I wanted to knw how to get the last 10 SQL Statments issued by the
> users client to the SQL Server.
> dbcc inputbuffer (spid) gives us the last statement how to get a
> history of statments by a SPID.
>
> Thanks
>

Activity Captured by profiler

Hi
Does the profiler catch every activity on the server. I mean what about
the various username & password screens , dont you think then its a
threat to security .
& I wanted to knw how to get the last 10 SQL Statments issued by the
users client to the SQL Server.
dbcc inputbuffer (spid) gives us the last statement how to get a
history of statments by a SPID.
ThanksHi
Profiler will not display sensitive data such as passwords etc. If you are
worried about passwords and usernames you should also use protocol encryptio
n
between client and server.
You can use a filter in profiler to restict information display to a given
SPID or use the search options to jump between entries for a given SPID.
John
"Double_B" wrote:

> Hi
> Does the profiler catch every activity on the server. I mean what about
> the various username & password screens , dont you think then its a
> threat to security .
> & I wanted to knw how to get the last 10 SQL Statments issued by the
> users client to the SQL Server.
> dbcc inputbuffer (spid) gives us the last statement how to get a
> history of statments by a SPID.
>
> Thanks
>

Activity Captured by profiler

Hi
Does the profiler catch every activity on the server. I mean what about
the various username & password screens , dont you think then its a
threat to security .
& I wanted to knw how to get the last 10 SQL Statments issued by the
users client to the SQL Server.
dbcc inputbuffer (spid) gives us the last statement how to get a
history of statments by a SPID.
ThanksHi
Profiler will not display sensitive data such as passwords etc. If you are
worried about passwords and usernames you should also use protocol encryption
between client and server.
You can use a filter in profiler to restict information display to a given
SPID or use the search options to jump between entries for a given SPID.
John
"Double_B" wrote:
> Hi
> Does the profiler catch every activity on the server. I mean what about
> the various username & password screens , dont you think then its a
> threat to security .
> & I wanted to knw how to get the last 10 SQL Statments issued by the
> users client to the SQL Server.
> dbcc inputbuffer (spid) gives us the last statement how to get a
> history of statments by a SPID.
>
> Thanks
>