create table permission denied

Hi,

i run an asp.net application which uses sql server express.
i defined a login 'aspnet' (IIS 5.0) and for the specific database, an user
'aspnet' with following roles:
db_datareader and db_datawriter.

Now, any user who uses that application must also be able to create
programmatically tables in that database. My question is: which role do i
have to give to user 'aspnet'?

I use Studio Management express.

Thanks
Tartuffe

The answer depends on which version of SQL Server you are using, since SQL Server 2005 dramatically enhanced permissions in the database. Which version?

Also, must the user be able to create ANY table in the database, or only a specific table? I think the former, but please confirm.

Don

|||

Hi, thanks for replying.

I use sql server 2005 express with Studio Management express.

Each windows-account in the domain in our organization who starts the application creates automatically (in code-behnd) a table with his personal (unique) number in the organization (e.g. L0564).

This happens only the first time he starts the application. So each member has his own table.

We use IIS 5.1, so the account which runs under asp.net is ASPNET. For another application, i defined a login in Studio Management and then for the database of that application, i defined a user 'aspnet' with following roles: db_reader and db_writer (i didn't use schema's because it's not very clear to me ..). This works, but there was no need to cerate a table.

I did the same for this new application +I db_owner and then it works. But i think it's probably too many privileges ... So: my question is: which privileges to give to 'aspnet' and how to do that in Studio Management?

Thanks

|||

You need the dbowner... only then you will be able to do the specific operations like creating tables and other things. DBWrite and Read will allow you to do simple updates insert and deletes.

|||

Thanks. I'll try.

If you don't mind, .. what if the ASPNET account must also be able to create databases? Is it suffisant with db_owner only?

|||

Yes i think so .. it doesn't matter which account it is .. till the time you map correct roles

|||

i tried with db_owner and Aspnet can create tables programmatically.

But Aspnet cannot create a new database with only db_owner.

|||

you need to be sysadmin for that

|||

Thanks

sql

create table permission denied

Hello,
I hope this is something simple to fix, but so far I had no luck...
Situation:
I created sql login, let's say 'test' and allowed access to database
db1. Actually, I made the account as the db_owner of db1. The test
account was not added to any of the server roles. Now, everything is
working fine until I try to create table or view or proc, etc. in the
db1 while logged in as the test user.
When creating table I get that CREATE TABLE permission denied in
database 'db1'. I tried executing GRANT CREATE TABLE after logging on as
sysadmin to that test account. That didn't help. IS there some DENY
somewhere that I don't see? How can I check what is preventing me from
creating table using that account?
Any comments?
Thanksgot it ... database role 'public' had DENY on creating tables. So even
if my test login had GRANT, the DENY on public denied for test as well
because test is public and db_owner.
laimis wrote:
> Hello,
> I hope this is something simple to fix, but so far I had no luck...
> Situation:
> I created sql login, let's say 'test' and allowed access to database
> db1. Actually, I made the account as the db_owner of db1. The test
> account was not added to any of the server roles. Now, everything is
> working fine until I try to create table or view or proc, etc. in the
> db1 while logged in as the test user.
> When creating table I get that CREATE TABLE permission denied in
> database 'db1'. I tried executing GRANT CREATE TABLE after logging on as
> sysadmin to that test account. That didn't help. IS there some DENY
> somewhere that I don't see? How can I check what is preventing me from
> creating table using that account?
> Any comments?
> Thanks

Create new sql server login

Hi,
Is there any way to execute sp_addlogin stored procedure to
create a new sql server login from a login account who has minimum
privileges on database. (He is not a member of either symin or security
admin).
My problem is I need to create new sql server login from code
from my application. At runtime the DBA may not willing to give a login with
symin for my application use. So I am looking for an alternative to
execute sp_addlogin procedure with minimum privileged account.
Please help me.
VenkatWhat about use a role for from code?
"venkat" wrote:

> Hi,
>
> Is there any way to execute sp_addlogin stored procedure to
> create a new sql server login from a login account who has minimum
> privileges on database. (He is not a member of either symin or security
> admin).
>
> My problem is I need to create new sql server login from code
> from my application. At runtime the DBA may not willing to give a login wi
th
> symin for my application use. So I am looking for an alternative to
> execute sp_addlogin procedure with minimum privileged account.
>
> Please help me.
>
> --
> Venkat
>
>|||Actually my application is a multi user application. So if a new user
account need to be created then the corresponding database access also be
created. So in this case the logged in user (Administrator defined in
application) has permission to create new user. So my point is if we want to
create a new sql server login the logged in user (in this case
administrator) must be member of symin and securityadmin. But at the
deployment side we are facing problem as there are no permissions on db.
(Means not a member of symin). So I am looking for an alternative to come
out of this problem by using any stored procedures which can be run by a
normal user.
"Enric" <Enric@.discussions.microsoft.com> wrote in message
news:744BCE5E-5968-4FC6-9BE8-5710F70C50FE@.microsoft.com...
> What about use a role for from code?
> "venkat" wrote:
>|||In SQL Server 2005, you have additional options except symin or securitya
dmin. Are you on 2005?
If no, you need to be either symin or securityadmin. Only way around that
would be to create your
own version of the sp_addlogin stored procedure (see the source code for tha
t procedure and you will
see that it checks against securityadmin).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"venkat" <tammana@.inooga.com> wrote in message news:e6$%23j05DGHA.2036@.TK2MSFTNGP14.phx.gbl
..
> Actually my application is a multi user application. So if a new user acco
unt need to be created
> then the corresponding database access also be created. So in this case th
e logged in user
> (Administrator defined in application) has permission to create new user.
So my point is if we
> want to create a new sql server login the logged in user (in this case adm
inistrator) must be
> member of symin and securityadmin. But at the deployment side we are fa
cing problem as there
> are no permissions on db. (Means not a member of symin). So I am lookin
g for an alternative to
> come out of this problem by using any stored procedures which can be run b
y a normal user.
> "Enric" <Enric@.discussions.microsoft.com> wrote in message
> news:744BCE5E-5968-4FC6-9BE8-5710F70C50FE@.microsoft.com...
>|||venkat (tammana@.inooga.com) writes:
> Is there any way to execute sp_addlogin stored procedure to
> create a new sql server login from a login account who has minimum
> privileges on database. (He is not a member of either symin or security
> admin).
>
> My problem is I need to create new sql server login from code
> from my application. At runtime the DBA may not willing to give a login
> with symin for my application use. So I am looking for an alternative
> to execute sp_addlogin procedure with minimum privileged account.
Assuming that you are on SQL 2000, the one way I can think of is
application role. (SQL 20005 has more options). You would in that
case only use the application role for this particular task, and this
application role must exist in master. I have not tested that this
actually works, though.
I will have to say that I am not sure that this sounds like a very good
idea. Anyone who adds logins, should probably have explicit access to
do so, as this is a quite serious thing to do. This is paricularly
important if the application is to run a server together with many
other applications. I don't think a DBA would accept an application
whihc permitted users to create logins on his server.
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

Create new login non case sensitive

I am trying to create a new login on a database, and I want that username and password NOT to be case sensitive. Do I have to set this at the server level or can I set this in the SQL script?

Here is the script I am currently using...

IF EXISTS (SELECT * FROM dbo.sysusers WHERE [name] = 'UName')

BEGIN

EXEC sp_dropuser 'UserName'

EXEC sp_droplogin 'UserName'

END

EXEC sp_addlogin 'UserName', 'Password', 'Database'

GO

EXEC sp_adduser 'UserName', 'Password', 'db_datareader'

GO

Any help would be appreciated...

David

SQL Server 2005 only uses case sensitive password, this is an intentional behavior change for SQL Server 2005.See http://msdn2.microsoft.com/en-us/library/ms143359.aspx for more details.

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Also, the sp_adduser statement is incorrect - there is no password for users; the second parameter of sp_adduser is the user's name in the database. See http://msdn2.microsoft.com/en-us/library/ms181422.aspx for more information on sp_adduser.

Thanks
Laurentiu

Create Logon

Newbie question
Following script works OK against a SQL Server 2005
CREATE LOGIN [LoginX] WITH PASSWORD = 'PasswordX',DEFAULT_DATABASE=[DBX]
But against a SQL Server 2000 I get "Incorrect syntax near 'LOGIN'."
How do I change the script to work for 2000?
/BroedenBroeden wrote:
> Newbie question
> Following script works OK against a SQL Server 2005
> CREATE LOGIN [LoginX] WITH PASSWORD => 'PasswordX',DEFAULT_DATABASE=[DBX]
> But against a SQL Server 2000 I get "Incorrect syntax near 'LOGIN'."
> How do I change the script to work for 2000?
> /Broeden
>
look in BOL for sp_addlogin:
sp_addlogin [ @.loginame = ] 'login'
[ , [ @.passwd = ] 'password' ]
[ , [ @.defdb = ] 'database' ]
[ , [ @.deflanguage = ] 'language' ]
[ , [ @.sid = ] sid ]
[ , [ @.encryptopt= ] 'encryption_option' ]

Create Login within a stored procedure

I want to create a login from within a stored procedure; passing in a
User name and a password.
It keeps giving me an error when I use a variable as the password.
what am I doing wrong?
CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]One hint.
Square brackets have the same effect as single quotes.
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Jayme" <jayloub@.comcast.net> wrote in message
news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...
>I want to create a login from within a stored procedure; passing in a
> User name and a password.
> It keeps giving me an error when I use a variable as the password.
> what am I doing wrong?
>
> CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]
>|||Unfortunately, CREATE LOGIN doesn't accept parameters so you need to use
dynamic SQL. The example uses QUOTENAME to properly enclose the values and
handle embedded quotes.
CREATE PROC dbo.usp_CreateLogin
@.Login sysname,
@.Password sysname
AS
DECLARE @.CreateLoginStatement nvarchar(200)
SET @.CreateLoginStatement = 'CREATE LOGIN ' +
QUOTENAME(@.Login) +
' WITH PASSWORD = ' +
QUOTENAME(@.Password, '''')
EXEC sp_executesql @.CreateLoginStatement
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Jayme" <jayloub@.comcast.net> wrote in message
news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...
>I want to create a login from within a stored procedure; passing in a
> User name and a password.
> It keeps giving me an error when I use a variable as the password.
> what am I doing wrong?
>
> CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]
>|||Thanks, that worked.
Dan Guzman wrote:[vbcol=seagreen]
> Unfortunately, CREATE LOGIN doesn't accept parameters so you need to use
> dynamic SQL. The example uses QUOTENAME to properly enclose the values an
d
> handle embedded quotes.
> CREATE PROC dbo.usp_CreateLogin
> @.Login sysname,
> @.Password sysname
> AS
> DECLARE @.CreateLoginStatement nvarchar(200)
> SET @.CreateLoginStatement = 'CREATE LOGIN ' +
> QUOTENAME(@.Login) +
> ' WITH PASSWORD = ' +
> QUOTENAME(@.Password, '''')
> EXEC sp_executesql @.CreateLoginStatement
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jayme" <jayloub@.comcast.net> wrote in message
> news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...

Create Login within a stored procedure

I want to create a login from within a stored procedure; passing in a
User name and a password.
It keeps giving me an error when I use a variable as the password.
what am I doing wrong?
CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]One hint.
Square brackets have the same effect as single quotes.
--
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Jayme" <jayloub@.comcast.net> wrote in message
news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...
>I want to create a login from within a stored procedure; passing in a
> User name and a password.
> It keeps giving me an error when I use a variable as the password.
> what am I doing wrong?
>
> CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]
>|||Unfortunately, CREATE LOGIN doesn't accept parameters so you need to use
dynamic SQL. The example uses QUOTENAME to properly enclose the values and
handle embedded quotes.
CREATE PROC dbo.usp_CreateLogin
@.Login sysname,
@.Password sysname
AS
DECLARE @.CreateLoginStatement nvarchar(200)
SET @.CreateLoginStatement = 'CREATE LOGIN ' +
QUOTENAME(@.Login) +
' WITH PASSWORD = ' +
QUOTENAME(@.Password, '''')
EXEC sp_executesql @.CreateLoginStatement
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Jayme" <jayloub@.comcast.net> wrote in message
news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...
>I want to create a login from within a stored procedure; passing in a
> User name and a password.
> It keeps giving me an error when I use a variable as the password.
> what am I doing wrong?
>
> CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]
>|||Thanks, that worked.
Dan Guzman wrote:
> Unfortunately, CREATE LOGIN doesn't accept parameters so you need to use
> dynamic SQL. The example uses QUOTENAME to properly enclose the values and
> handle embedded quotes.
> CREATE PROC dbo.usp_CreateLogin
> @.Login sysname,
> @.Password sysname
> AS
> DECLARE @.CreateLoginStatement nvarchar(200)
> SET @.CreateLoginStatement = 'CREATE LOGIN ' +
> QUOTENAME(@.Login) +
> ' WITH PASSWORD = ' +
> QUOTENAME(@.Password, '''')
> EXEC sp_executesql @.CreateLoginStatement
> GO
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Jayme" <jayloub@.comcast.net> wrote in message
> news:1152628773.520426.185450@.s13g2000cwa.googlegroups.com...
> >I want to create a login from within a stored procedure; passing in a
> > User name and a password.
> > It keeps giving me an error when I use a variable as the password.
> > what am I doing wrong?
> >
> >
> > CREATE LOGIN [@.aUserName] WITH PASSWORD= [@.aPassword]
> >

CREATE LOGIN vs. sp_addlogin

I know in SQL 2005, you can use the CREATE LOGIN. Along with that, you can
use also use: CHECK_EXPIRATION = and CHECK_POLICY =
When using the sp_addlogin in SQL 2000, is there anything equivelent to the
CHECK_EXPIRATION and CHECK_POLICY I can use? Any sp_?
Thanks,Nope. There is no password policy for SQL logins under SQL
Server 2000. The functionality was added to SQL Server 2005.
-Sue
On Mon, 14 Aug 2006 14:49:02 -0700, SteveS
<SteveS@.discussions.microsoft.com> wrote:

>I know in SQL 2005, you can use the CREATE LOGIN. Along with that, you can
>use also use: CHECK_EXPIRATION = and CHECK_POLICY =
>When using the sp_addlogin in SQL 2000, is there anything equivelent to the
>CHECK_EXPIRATION and CHECK_POLICY I can use? Any sp_?
>Thanks,
>|||Sue,
Thanks for the answer. It was just what I was looking for.
Steve
"Sue Hoegemeier" wrote:

> Nope. There is no password policy for SQL logins under SQL
> Server 2000. The functionality was added to SQL Server 2005.
> -Sue
> On Mon, 14 Aug 2006 14:49:02 -0700, SteveS
> <SteveS@.discussions.microsoft.com> wrote:
>
>

Create login problem in SQL Server 2005

Hey guys,

I'm having a problem making a new login inside the sql management studio, the problem is, when i create a new login, i selected SQL Authentication, then type a password, then uncheck Enforce password policy.

i then select the database i want the login to be associated with, but once i click ok i get this exception:
Create failed for Login ''. (Microsoft.SqlServer.Smo)

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
"An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name. (Microsoft SQL Server, Error: 1038)

I even tried with Northwind and a brand new database with a table and 2 columns but it's the same story every time.
Any ideas?

Thanks a bunchMake sure you have entered "Login Name" in the text box provided at the top of the window

thanks
Anoop

CREATE LOGIN problem

I know this is a really stupid question but I can't figure out how to make this work. It just doesn't seem to want variables. What is the proper syntax?

CREATE LOGIN [@.GUserName] FROM WINDOWS WITH DEFAULT_DATABASE=@.DBName, DEFAULT_LANGUAGE=[@.LoginLanguage]

You could put the command into a variable, and then execute the variable:

declare @.Command varchar(2000)

Select @.Command = 'CREATE LOGIN [' + @.GUserName + '] FROM WINDOWS WITH DEFAULT_DATABASE= ' + @.DBName + ' , DEFAULT_LANGUAGE=['+@.LoginLanguage +']'

exec (@.command)

BobP

|||

That didn't work...but this did....

declare @.tmp varchar(2000)

SET @.tmp = 'CREATE LOGIN [' + @.GUserName + '] FROM WINDOWS WITH DEFAULT_DATABASE= ' + @.DBName + ' , DEFAULT_LANGUAGE=['+@.LoginLanguage +']'

execute sp_executesql @.tmp

|||

Actually, this is the SET command that finally worked...

SET @.tmp = 'CREATE LOGIN ['+@.SQLLoginName+'] WITH PASSWORD = '''+@.Password+''', DEFAULT_DATABASE='+@.DBName+', DEFAULT_LANGUAGE='+@.LoginLanguage+''

|||

Actually, to make your code more robust, you should use:

SET @.tmp = 'CREATE LOGIN ' + quotename(@.SQLLoginName)
+ ' WITH PASSWORD = ' + quotename(@.Password, '''')
+ ', DEFAULT_DATABASE = ' + quotename(@.DBName)
+ ', DEFAULT_LANGUAGE= ' + quotename(@.LoginLanguage)

Otherwise, the code is open to injection. quotename will do the proper quoting and escaping, so you should always use it when building dynamic SQL commands.

Thanks
Laurentiu

Create Login like another login...

One bit of functionality I like in Oracle's Enterprise manager, that I
haven't located in SQL Server is the ability to create a new login with all
the same rights, roles etc of an existing user.
I am using SQL Server 2005. I have tried
USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN written by GREG LARSEN, but I ge
t
an error when executing it.
==================
Msg 213, Level 16, State 7, Line 2
Insert Error: Column name or number of supplied values does not match table
definition.
==================
Does anyone point me to a way of doing this?
PaulThat's a pretty old script you're using and it's written for SQL Server
2000, calling objects in the master database.
You can update the script using some of the security catalog views instead
of tables in master. For example, fn_my_permissions will tell you what
effective permissions the current user has.
As for the error message below, it looks like you probably had a typo
somewhere when creating or running the sproc. Probably a datatype mismatch.
joe.
"Paul" <Paul@.discussions.microsoft.com> wrote in message
news:CFECA19B-9C34-4D52-B9FC-1FA95817EE78@.microsoft.com...
> One bit of functionality I like in Oracle's Enterprise manager, that I
> haven't located in SQL Server is the ability to create a new login with
> all
> the same rights, roles etc of an existing user.
> I am using SQL Server 2005. I have tried
> USP_GEN_USER_RIGHTS_BASED_ON_EXISTING_LO
GIN written by GREG LARSEN, but I
> get
> an error when executing it.
> ==================
> Msg 213, Level 16, State 7, Line 2
> Insert Error: Column name or number of supplied values does not match
> table
> definition.
> ==================
> Does anyone point me to a way of doing this?
> Paul

Create login from certificate question.

Howdy all. Can someone please point me to a good white paper that will show
me how to make use/ the benefits of this feature?
TIA, ChrisRHi Chris
I don't know of any white paper but Raul Garcia's and Laurentiu Cristofor's
blog
have examples of using certificates and creating logins from them for example:
http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx
http://blogs.msdn.com/lcris/archive/2005/06/15/429631.aspx
John
"ChrisR" wrote:
> Howdy all. Can someone please point me to a good white paper that will show
> me how to make use/ the benefits of this feature?
> TIA, ChrisR
>
>

Create Login form(authenticate with sql)

Hi all.

M trying to create a logon form
I had something in mind but i can work it out yet

When the user wants to login into the application the value of textbox must be compared with the datafield in the sql server.
And then i want a messagebox to show up.

I created a table in sql server with 2 fields in it , User and Password.

I hope anyone could help me tnx already

You will need a login which is able to access the table or a stored procedure which does the check of the login for you. Then its up to you to either raise an error from your stored procedure or return a specific result fromthe stored procedure which is transformed into a user friendly message like "Password wrong" or "Username / password combination wrong".

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

create login for existing database user

Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!
See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troubleshoo...phan_users.htm
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!
|||I think because of you have orphaned users.
Try to use 'sp_change_users_login' you can find detailed information on BOL.
"Ezeki?l" wrote:

> Hello,
> I've this problem with a database user that was restored from a backup. If i
> want to re-create the same username for sql login, it fails. This also
> happens when i try to delete the user to set the ownership to someone else.
> Is it possible to work around it.
> THX!
>
>
|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.
|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.[vbcol=seagreen]
>
> "Ezekil" wrote:
If i[vbcol=seagreen]
else.[vbcol=seagreen]
|||Hi,
I assume that you have the login already exists in SQL Server.
Let's say username : user login_name: login
sp_change_users_login 'UPDATE_ONE', 'username', 'login'
Regards..
"Ezeki?l" wrote:

> Hi,
> I tried to run sp_change_users_login from the query analyzer, but with no
> success. Could you give me an example how to use it?
> THNX!
> "Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
> news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> BOL.
> If i
> else.
>
>

create login for existing database user

Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troubleshooting_orphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekiël" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.
>
> "Ezekiël" wrote:
> > Hello,
> >
> > I've this problem with a database user that was restored from a backup.
If i
> > want to re-create the same username for sql login, it fails. This also
> > happens when i try to delete the user to set the ownership to someone
else.
> >
> > Is it possible to work around it.
> >
> > THX!
> >
> >
> >

create login for existing database user

Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!See sp_change_users_login in SQL Server 2000 Books Online. I have some code
that identifies all such logins. For more information on this, go to:
http://vyaskn.tripod.com/troublesho...rphan_users.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Ezekil" <ezekiel@.lycos.nl> wrote in message
news:uQsqA3lbEHA.1292@.TK2MSFTNGP11.phx.gbl...
Hello,
I've this problem with a database user that was restored from a backup. If i
want to re-create the same username for sql login, it fails. This also
happens when i try to delete the user to set the ownership to someone else.
Is it possible to work around it.
THX!|||I think because of you have orphaned users.
Try to use 'sp_change_users_login' you can find detailed information on BOL.
"Ezeki?l" wrote:

> Hello,
> I've this problem with a database user that was restored from a backup. If
i
> want to re-create the same username for sql login, it fails. This also
> happens when i try to delete the user to set the ownership to someone else
.
> Is it possible to work around it.
> THX!
>
>|||Hello
Please refer to the following article for more information on this topic :
240872 HOW TO: Resolve Permission Issues When You Move a Database Between
http://support.microsoft.com/?id=240872
Thank you for using Microsoft newsgroups.
Sincerely
Pankaj Agarwal
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi,
I tried to run sp_change_users_login from the query analyzer, but with no
success. Could you give me an example how to use it?
THNX!
"Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> I think because of you have orphaned users.
> Try to use 'sp_change_users_login' you can find detailed information on
BOL.[vbcol=seagreen]
>
> "Ezekil" wrote:
>
If i[vbcol=seagreen]
else.[vbcol=seagreen]|||Hi,
I assume that you have the login already exists in SQL Server.
Let's say username : user login_name: login
sp_change_users_login 'UPDATE_ONE', 'username', 'login'
Regards..
"Ezeki?l" wrote:

> Hi,
> I tried to run sp_change_users_login from the query analyzer, but with no
> success. Could you give me an example how to use it?
> THNX!
> "Umut Nazlica" <UmutNazlica@.discussions.microsoft.com> wrote in message
> news:5C2324D3-ACB0-45DE-A89F-22DA6A8E5CD8@.microsoft.com...
> BOL.
> If i
> else.
>
>

create login for db access on the fly

Hi,
How can i create logins on the fly to access a database.
I have a login which can access a database through my webapp. The enduser
will access also through my webapp, but they have a different login to the
database.
My question how can this be done? I've tried to execute the following, but
that did not work.
EXEC sp_addlogin 'username','password';
EXEC sp_defaultdb 'username', 'database';
use database;
EXEC sp_grantdbaccess 'username';
EXEC sp_addrolemember 'standard','username';
result:
Server: Msg 15247, Level 16, State 1, Procedure sp_addlogin, Line 17
User does not have permission to perform this action.
Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
Cannot change default database belonging to someone else.
Granted database access to 'jos'.
'username' added to role 'standard'.Use either Windows Authentication or Application Roles.
I'm not sure in what context it would make sense to add users
"on-the-fly". To add a user requires a login to the database with the
db_accesmin role anyway. Normally it's easier to add domain user
groups rather than individual users.
David Portas
SQL Server MVP
--|||Hi David,
I agree with you, but it is for emergency app which needs to create logins
for authenication means, because the user will access the database through
the webapp and doens't know their database password.
The user will be added to a databaserole.
Could you help me build this functionality?
"David Portas" wrote:

> Use either Windows Authentication or Application Roles.
> I'm not sure in what context it would make sense to add users
> "on-the-fly". To add a user requires a login to the database with the
> db_accesmin role anyway. Normally it's easier to add domain user
> groups rather than individual users.
> --
> David Portas
> SQL Server MVP
> --
>|||You need symin, db_accesmin or db ownership to modify users and
permissions. The error message indicated that you didn't have those
permissions so you'll have to use a login that does.
David Portas
SQL Server MVP
--|||Depends what you mean by on the fly, but, if you want the users to access th
e
DB w/o knowing the pwd or accnt they are going in as (ie, they can log into
your site (web-wise) with an accnt and pwd but knowing their accnt and pwd
alone won't give them direct access to the DB) you could set up SQL
Authentication on the DB, capture the users accnt value, modify it (in a
fixed way) and use the modified accnt value and a pwd in a COM or .NET
Assembly to actually access the DB. Means a bit more work on the DB end
tho... Or, you could access the DB with a specific accnt/pwd and pass in th
e
users web accnt as a parameter to your sprocs so you'd know who had requeste
d
the access... not sure what your ultimate goal is.
"Ezeki?l" wrote:

> Hi,
> How can i create logins on the fly to access a database.
> I have a login which can access a database through my webapp. The enduser
> will access also through my webapp, but they have a different login to the
> database.
> My question how can this be done? I've tried to execute the following, but
> that did not work.
> EXEC sp_addlogin 'username','password';
> EXEC sp_defaultdb 'username', 'database';
> use database;
> EXEC sp_grantdbaccess 'username';
> EXEC sp_addrolemember 'standard','username';
> result:
> Server: Msg 15247, Level 16, State 1, Procedure sp_addlogin, Line 17
> User does not have permission to perform this action.
> Server: Msg 15132, Level 16, State 1, Procedure sp_defaultdb, Line 14
> Cannot change default database belonging to someone else.
> Granted database access to 'jos'.
> 'username' added to role 'standard'.
>

Create login and user

Hi,

I’m new in this.

I’m trying to create a user with the following code, but in the yellow part it throw an error saying the user can’t be created. Please if somebody can say what the problem is or correct the problem for me I will be thankful:

Public Sub CreateUser()

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

'conn.DatabaseName = "VideoDB"

Dim myServer As New Server(conn)

If Not myServer.Logins.Contains(TextBox2.Text) Then

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text)

Dim sys_database As New Database(myServer, "VideoDB")

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.Create()

If CheckBox18.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table

db_table.Schema = "admin"

db_table.Name = "customers"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table.Name = "capturepicture"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table.Name = "items"

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table.Name = "properties"

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

MsgBox(db_login.Script)

MsgBox(db_user.Script)

End If

End Sub

Regarding the samples located on http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=195387&SiteID=1 you did just fine beside the logon Type. Which error do you get during the creation of the user ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

The only thing I see wrong with your code is four lines above the highlighted line, where the code is:

db_login.Create(TextBox3.Text)

Now, everywhere else you're referring to TextBox2.Text, but in the create of the login you're using TextBox3.Text. It may be failing due to that mismatch. Otherwise the code looks correct.

|||

Hi,

In the textbox2 I refer the username in the textbox3 I refer the password.

I change the blue part and know it creates the user, but know the problem is in the tables privileges. It throws this error:

object Microsoft.SqlServer.Management.Smo.Table does not live under Server.

Public Sub CreateUser()

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

Dim myServer As New Server(conn)

If Not myServer.Logins.Contains(TextBox2.Text) Then

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text)

Dim sys_database As Database = myServer.Databases("VideoDB")

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.Create()

If CheckBox18.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table

db_table.Schema = "admin"

db_table.Name = "customers"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table.Name = "capturepicture"

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table.Name = "items"

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table.Name = "properties"

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

MsgBox(db_login.Script)

MsgBox(db_user.Script)

End If

End Sub

|||

Hi,

Now it Works. Thanks for the help.

Code:

Public Sub CreateUser()

‘stablish connection with sqlserver authentication

Dim conn As New ServerConnection("STATION01\SQLEXPRESS", "admin", "a9078564")

Dim myServer As New Server(conn)

‘Verify if the user already exist

If Not myServer.Logins.Contains(TextBox2.Text) Then

‘Create the login

Dim db_login As New Login(myServer, TextBox2.Text)

db_login.LoginType = LoginType.SqlLogin

db_login.Create(TextBox3.Text, LoginCreateOptions.None)

Dim sys_database As Database = myServer.Databases("VideoDB")

‘Create the user

Dim db_user As New User(sys_database, TextBox2.Text)

db_user.Login = TextBox2.Text

db_user.UserType = UserType.SqlLogin

db_user.Create()

If CheckBox18.Checked Then

‘Grant roles

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_owner")

Else

‘Grant privileges

Dim SelectPrivilege As New ObjectPermissionSet

SelectPrivilege.Select = True

Dim InsertPrivilege As New ObjectPermissionSet

InsertPrivilege.Insert = True

Dim UpdatePrivilege As New ObjectPermissionSet

UpdatePrivilege.Update = True

Dim DeletePrivilege As New ObjectPermissionSet

DeletePrivilege.Delete = True

Dim db_table As New Table(sys_database, "customers", "admin")

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox2.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox3.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox4.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

db_table = New Table(sys_database, "capturepicture", "admin")

If CheckBox1.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

db_table = New Table(sys_database, "items", "admin")

If CheckBox5.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox6.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox7.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If CheckBox8.Checked Then db_table.Grant(DeletePrivilege, TextBox2.Text)

If CheckBox9.Checked Then

db_login.AddToRole("securityadmin")

db_user.AddToRole("db_securityadmin")

db_user.AddToRole("db_accessadmin")

db_user.AddToRole("CreateUser")

End If

db_table = New Table(sys_database, "properties", "admin")

If CheckBox10.Checked Then db_table.Grant(SelectPrivilege, TextBox2.Text)

If CheckBox11.Checked Then db_table.Grant(InsertPrivilege, TextBox2.Text)

If CheckBox12.Checked Then db_table.Grant(UpdatePrivilege, TextBox2.Text)

If RadioButton1.Checked Then

db_user.AddToRole("TransactionsFullAccess")

Else

db_user.AddToRole("TransactionsRestrictedAccess")

End If

If CheckBox15.Checked Then db_user.AddToRole("Promotions")

If CheckBox18.Checked Then db_user.AddToRole("History")

End If

End If

End Sub

create login alias DBO

Hi:

When I set up databases for my users, I create them a user and alias it as the DBO. They can then use this user in their code.

The problem is that if a user is aliased as a DBO, they can change the size of their database, create jobs, backup their database, set up replication (from their own sql server), etc...

Is there a different way to create their login (with minimal administrative effort) to give them access to create objects in their database, without allowing them access to these other tasks?

Thanksyou can use the database role'db_ddladmin'. This role is allowed to create, modify and drop all database objects, but cannot issue security-related commands (grant, ...).|||Originally posted by jora
you can use the database role'db_ddladmin'. This role is allowed to create, modify and drop all database objects, but cannot issue security-related commands (grant, ...).

And all objects will belong to this user: like tom.newtable. It needs to be careful - there is possibility to have couple tables with the same name but with different owners.

BOL:For example, if user Andrew is a member of the sysadmin fixed server role and creates a table T1, T1 belongs to dbo and is qualified as dbo.T1, not as Andrew.T1. Conversely, if Andrew is not a member of the sysadmin fixed server role but is a member only of the db_owner fixed database role and creates a table T1, T1 belongs to Andrew and is qualified as Andrew.T1. The table belongs to Andrew because he did not qualify the table as dbo.T1.|||I agree about the owner of the objects. As I understood the mail though (and I could be wrong offcourse) tommy only used the dbo user so the login was able to create objects. Thefore I see no problem, the user can still use his login in his/her applications.|||Thanks to all of you for your input. I love this forum, everyone is so helpful!

Ok, moving forward, may be able to use that type of login. However, I can see where it would mess up object owners.

For now, I will start by denying users to certain functions I want to lock out. For example, DENY BACKUP loginname.

Does anyone know how to deny a user from creating a job? I can not find it in BOL.|||I just remove the guest user from msdb, myself. So long as no one needs to save DTS packages there, it should work out ok.|||MCrowley, you rock! Great idea, I actually have denied access to create DTS packages on the server. One question, however, if I remove the quest user from the MSDB, will users still be able to DTS from their OWN machine? For example, they schedule a package on their own machine that pulls down updates, or whatever. Also exporting tables from their own database?

I'm going to test now.

Thanks!|||I removed the Guest login from the MSDB database, and the user (aliased as DBO of their own database), and still change the size of their database, log, and can still backup their database. It throws errors, that it can't write to the MSDB database, and the user is not a "user" in the MSDB, but it still works. It does, however, disallow them from creating jobs on the server!

Any other ideas on how to deny users changing the size of their database and backing it up? I tried:
USE DBNAME
GO
DENY BACKUP DATABASE TO LoginName
But since the login is aliased as a DBO, I get the error:
There is no such user or group LoginName.|||I just ran a test. You can take the users out of the db_owner role and put them in the db_ddladmin and db_securityadmin roles. You can even create your own special "db_owner" role that includes these for ease of administration. This will grant them the ability to create tables as dbo, but as Snail pointed out they will more than likely create tables in their own schemas only. The developers are going to have to be trained to specify the owner while creating objects. As this is possible when you add the user to the db_owner role, anyway, there should be only a few problems to start that gradually go away.
Hope this helps you in your problem.|||MCrowley, thanks for the info, I will experiement with that moving forward. I do have a bunch of databases that are already in place, and I can't make a drastic change like that right now.

Remember that these users are aliased as the DBO, they are not the DBO, sa is the DBO. I need to figure out how to stop users from backing up, restoring, and changing the size of their db.

Thanks|||In the MSDB I denied the public role access to the sp_add_job, and this made it so they can not add jobs to the server! Great!

Now, I just have to figure out how to prevent them from backing up and changing the size (or recovery model) or their database.|||Preventing them from changing the recovery model should be easy enough.
revoke exec on sp_dboption from public

This may have some uninteded consequences, as Enterprise Manager most likely uses this procedure to display information as well as change it. If your users are using EM for all their DBAdmin needs, then they will be getting all sorts of errors when the look for database properties. This may not be a problem for you,a s you are trying to restrict this stuff, anyway.

In order to prevent the backing up of databases/transaction logs, you are going to have to take these guys out of db_owner. I do not know of any other way of restricting that functionality (which, naturally does not mean a way doesn't exist...).

Create login account

I have installed MSDE 2000 on my computer. When I try to use ODBC to connect
to the data source, it keeps asking me to enter User Name and Password. Can
someone please tell me how I can create an account to connect to the SQL data
source?
> I have installed MSDE 2000 on my computer. When I try to use ODBC to
connect
> to the data source, it keeps asking me to enter User Name and Password.
Can
> someone please tell me how I can create an account to connect to the SQL
data
> source?
Here is a good starting point for MSDE security:
http://support.microsoft.com/default...;en-us;325022.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com