creating a user with dbo access

Hi,

I need to create a user with dbo access to a specific database using sql. I created a login and added a user to the login. How do i grant dbo privilege to this user (using sql)? Is there any system stored procedure for this? Please help.

Thanks in advance

Hi. Try

sp_changeowner [@.loginame=]'login'

Changes the owner of the current database.

sql

creating a user stored proc

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

ThanksCan you post a repro with procedure you are trying to create?|||

there's nothing really special about the proc.. Any proc I create becomes a system proc in the mster db...

e.g.

CREATEPROCEDURE test

AS

BEGIN

print'a'

END

GO

will behave like this.. I don't think this has anything to do with the actual proc I'm trying to use...

Thanks..

|||if you want to create the system stored procedure then use master and create your procedure started with sp_abc other wise create your sp on your desired database.|||There is no supported way to do this in SQL Server 2005. We are considering adding such features that will allow you to deploy SPs in one location and use it in context of multiple databases. For now, you will have to create the SP in each database. For admin type of SPs, you could use dynamic SQL within the SP.|||

I don't actually want to create system procs.. I want to create this proc in the master database and do not want it to be a system proc.. just a normal user proc.. I was able to do so since recently..But I think some thing has gone wrong and now when ever I create a proc, it gets created as a system proc... I did run the sp_MS_upd_sysobj_category with 2 but I understand that it's obsolete now...Any idea how I can turn this off? or atleast how this may have happened?

|||The feature I talked about will allow user SPs to behave like system SPs in terms of resolving object names in context of the db in which the SP is being executed. Anyway, for your problem I am not sure what can be done. The reason why we don't document certain system SPs is because it is for internal use and has severe implications if used incorrectly. I don't know if sp_MS_upd_sysobj_category code has changed in SQL Server 2005 or if it is some other SP call you did. But it looks like you will have to uninstall and install SQL Server or restore master from a last clean backup.

creating a user stored proc

I'm running mssql 2005. And any stored procedure I create in the master database gets created as system procedures since recently. I have created procs in the master database as user procs previously. As sp_MS_upd_sysobj_category is not supported in mssql 2005, does anyone know why this is happening.. or how I can rectify it?

ThanksCan you post a repro with procedure you are trying to create?|||

there's nothing really special about the proc.. Any proc I create becomes a system proc in the mster db...

e.g.

CREATE PROCEDURE test

AS

BEGIN

print 'a'

END

GO

will behave like this.. I don't think this has anything to do with the actual proc I'm trying to use...

Thanks..

|||if you want to create the system stored procedure then use master and create your procedure started with sp_abc other wise create your sp on your desired database.|||There is no supported way to do this in SQL Server 2005. We are considering adding such features that will allow you to deploy SPs in one location and use it in context of multiple databases. For now, you will have to create the SP in each database. For admin type of SPs, you could use dynamic SQL within the SP.|||

I don't actually want to create system procs.. I want to create this proc in the master database and do not want it to be a system proc.. just a normal user proc.. I was able to do so since recently..But I think some thing has gone wrong and now when ever I create a proc, it gets created as a system proc... I did run the sp_MS_upd_sysobj_category with 2 but I understand that it's obsolete now...Any idea how I can turn this off? or atleast how this may have happened?

|||The feature I talked about will allow user SPs to behave like system SPs in terms of resolving object names in context of the db in which the SP is being executed. Anyway, for your problem I am not sure what can be done. The reason why we don't document certain system SPs is because it is for internal use and has severe implications if used incorrectly. I don't know if sp_MS_upd_sysobj_category code has changed in SQL Server 2005 or if it is some other SP call you did. But it looks like you will have to uninstall and install SQL Server or restore master from a last clean backup.

Creating a user programmatically

Can I create a user programmatically (without using the ReportManager UI)?
If so, how?check this article:
http://blogs.msdn.com/bryanke/archive/2004/03/17/91736.aspx
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jessica Landisman" <Jessica Landisman@.discussions.microsoft.com> wrote in
message news:D578AACC-3F91-488C-8E69-2E2D8F8420E2@.microsoft.com...
> Can I create a user programmatically (without using the ReportManager
> UI)?
> If so, how?|||Lev,
Thanks for your prompt reply.
In my testing of this blog's suggestion to call GetPolicies() and then
SetPolicies(), SetPolicies only works for a user that is already in the RS
User table. Since I am trying to create a new RS user, that user is not in
the table. An exception is thrown by the SetPolicy method, and the exception
error message is "The user or group name 'Harry.Callahan' is not recognized".
"Lev Semenets [MSFT]" wrote:
> check this article:
> http://blogs.msdn.com/bryanke/archive/2004/03/17/91736.aspx
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jessica Landisman" <Jessica Landisman@.discussions.microsoft.com> wrote in
> message news:D578AACC-3F91-488C-8E69-2E2D8F8420E2@.microsoft.com...
> > Can I create a user programmatically (without using the ReportManager
> > UI)?
> > If so, how?
>
>|||SetPolicies should add user.
Can you try do it manually (in Report Manager)?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jessica Landisman" <JessicaLandisman@.discussions.microsoft.com> wrote in
message news:6D4AF936-94C3-4C7C-8AA5-558391AD62D5@.microsoft.com...
> Lev,
> Thanks for your prompt reply.
> In my testing of this blog's suggestion to call GetPolicies() and then
> SetPolicies(), SetPolicies only works for a user that is already in the RS
> User table. Since I am trying to create a new RS user, that user is not in
> the table. An exception is thrown by the SetPolicy method, and the
> exception
> error message is "The user or group name 'Harry.Callahan' is not
> recognized".
> "Lev Semenets [MSFT]" wrote:
>> check this article:
>> http://blogs.msdn.com/bryanke/archive/2004/03/17/91736.aspx
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Jessica Landisman" <Jessica Landisman@.discussions.microsoft.com> wrote
>> in
>> message news:D578AACC-3F91-488C-8E69-2E2D8F8420E2@.microsoft.com...
>> > Can I create a user programmatically (without using the ReportManager
>> > UI)?
>> > If so, how?
>>|||I can add a user manually in ReportManager.
I have performed SQL traces during when I add a user using ReportManager and
when I attempted to add a user using the code snippet you sent. The SQL
traces are very different.
"Lev Semenets [MSFT]" wrote:
> SetPolicies should add user.
> Can you try do it manually (in Report Manager)?
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Jessica Landisman" <JessicaLandisman@.discussions.microsoft.com> wrote in
> message news:6D4AF936-94C3-4C7C-8AA5-558391AD62D5@.microsoft.com...
> > Lev,
> > Thanks for your prompt reply.
> >
> > In my testing of this blog's suggestion to call GetPolicies() and then
> > SetPolicies(), SetPolicies only works for a user that is already in the RS
> > User table. Since I am trying to create a new RS user, that user is not in
> > the table. An exception is thrown by the SetPolicy method, and the
> > exception
> > error message is "The user or group name 'Harry.Callahan' is not
> > recognized".
> >
> > "Lev Semenets [MSFT]" wrote:
> >
> >> check this article:
> >> http://blogs.msdn.com/bryanke/archive/2004/03/17/91736.aspx
> >>
> >> --
> >> This posting is provided "AS IS" with no warranties, and confers no
> >> rights.
> >>
> >>
> >> "Jessica Landisman" <Jessica Landisman@.discussions.microsoft.com> wrote
> >> in
> >> message news:D578AACC-3F91-488C-8E69-2E2D8F8420E2@.microsoft.com...
> >> > Can I create a user programmatically (without using the ReportManager
> >> > UI)?
> >> > If so, how?
> >>
> >>
> >>
>
>|||that is strange.
Can we take this off this newsgroup?
Could you send me an e-mail?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jessica Landisman" <JessicaLandisman@.discussions.microsoft.com> wrote in
message news:F1631A44-47D7-4F07-B792-790CB03D94EE@.microsoft.com...
>I can add a user manually in ReportManager.
> I have performed SQL traces during when I add a user using ReportManager
> and
> when I attempted to add a user using the code snippet you sent. The SQL
> traces are very different.
> "Lev Semenets [MSFT]" wrote:
>> SetPolicies should add user.
>> Can you try do it manually (in Report Manager)?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Jessica Landisman" <JessicaLandisman@.discussions.microsoft.com> wrote in
>> message news:6D4AF936-94C3-4C7C-8AA5-558391AD62D5@.microsoft.com...
>> > Lev,
>> > Thanks for your prompt reply.
>> >
>> > In my testing of this blog's suggestion to call GetPolicies() and
>> > then
>> > SetPolicies(), SetPolicies only works for a user that is already in the
>> > RS
>> > User table. Since I am trying to create a new RS user, that user is not
>> > in
>> > the table. An exception is thrown by the SetPolicy method, and the
>> > exception
>> > error message is "The user or group name 'Harry.Callahan' is not
>> > recognized".
>> >
>> > "Lev Semenets [MSFT]" wrote:
>> >
>> >> check this article:
>> >> http://blogs.msdn.com/bryanke/archive/2004/03/17/91736.aspx
>> >>
>> >> --
>> >> This posting is provided "AS IS" with no warranties, and confers no
>> >> rights.
>> >>
>> >>
>> >> "Jessica Landisman" <Jessica Landisman@.discussions.microsoft.com>
>> >> wrote
>> >> in
>> >> message news:D578AACC-3F91-488C-8E69-2E2D8F8420E2@.microsoft.com...
>> >> > Can I create a user programmatically (without using the
>> >> > ReportManager
>> >> > UI)?
>> >> > If so, how?
>> >>
>> >>
>> >>
>>

Creating a user in SQLExpress with SQL Server Management Studio Express

I have created a user in SQL Server Management Studio Express. However,
whenever my ASP.NET application tries to connect I get the error
Cannot open database "ABC" requested by the login. The login failed. Login
failed for user 'Fred'.

If I change my login string to Integrated Security=SSPI it works. Also, I
was able to add a user successfully in SQL 2000 but SQLExpress is differnt.

Can anyone tell me how to add a user and give him permissions for a
particular database? Or refer me to a URL that does.

Many thanks.

In SQL Server Management Studio Express, when you login to the SQL Express Server, you'll see "Databases" as an item in the Object Explorer. If you expand that, you'll see all of your databases listed. Select the database you want and expand that, then you'll see Security as one of the items. Expand that one more time and you'll see Users. You can create a new user there, as well as right click on a user and select Properties to fine tune the permissions of that user.

You can also create global users (Logins) by going to the Security item at the top level list in the Object Explorer. Once you've created a Login, you can map it to certain users within a database.

Thanks< MJ

Creating a user in SQLExpress with SQL Server Management Studio Express

I have created a user in SQL Server Management Studio Express. However,
whenever my ASP.NET application tries to connect I get the error
Cannot open database "ABC" requested by the login. The login failed. Login
failed for user 'Fred'.
If I change my login string to Integrated Security=SSPI it works. Also, I
was able to add a user successfully in SQL 2000 but SQLExpress is differnt.
Can anyone tell me how to add a user and give him permissions for a
particular database? Or refer me to a URL that does.
Many thanks.
hi Andrew,
Andrew Chalk wrote:
> I have created a user in SQL Server Management Studio Express.
> However, whenever my ASP.NET application tries to connect I get the
> error Cannot open database "ABC" requested by the login. The login failed.
> Login failed for user 'Fred'.
> If I change my login string to Integrated Security=SSPI it works.
> Also, I was able to add a user successfully in SQL 2000 but
> SQLExpress is differnt.
> Can anyone tell me how to add a user and give him permissions for a
> particular database? Or refer me to a URL that does.
> Many thanks.
please verify your SQLExpress instance accepts standard SQL Server logins,
enabling mixed security.. your connection string changed to ...Integrated
Security=SSPI implies a trusted connection...
SQL Server uses a so called "2 phases" authentication policy:
first an SQL Server Login or a Windows login must be created of granted
access to the SQL Server instance... at the server level a login can be made
member of none, 1 or all of the fixed server roles, which include "sysadmin"
role and so on...
you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires user's
credential becouse it's directly provided by Windows via the logins'ID
(sid), which authenticate user's login at the windows login step... SQL
Server only needs to verify that the corresponding login and/or group is
granted to log on the instance...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to...
CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
GRANT {database permission}
http://msdn2.microsoft.com/en-us/library/ms178569.aspx
GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Andrea: Thanks you for your detailed reply.
I understand the concptual difference between Windows and SQL authentication
and thought I had done all the necessary steps, which is why I am puzzled.
As I mentioned, with SQL 2000 I used SQL Enterprise manager and have the
login working. I have tried to map the steps I took in SQL2000's SQL EM to
those in the new SQL Server Mgt. Studio Express.
1) I created a login under the server->security folder.
I did not give this Login any Roles. It is just a plain ol' user (public).
2) I went to the server instance name at the top and GRANTed "Connect SQL"
permission to the new Login just created;
3) I went to the database and granted "Connect" permission to the Login.
After these steps I get the error reported in my earlier post.
I should add that I am not an SQL Server expert and first looked at the SQL
Server Mgt. Studio Express BOL.They are absolutely useless.
Hope the above hels.
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40acn2F19erhvU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> please verify your SQLExpress instance accepts standard SQL Server logins,
> enabling mixed security.. your connection string changed to ...Integrated
> Security=SSPI implies a trusted connection...
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created of granted
> access to the SQL Server instance... at the server level a login can be
> made
> member of none, 1 or all of the fixed server roles, which include
> "sysadmin"
> role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires user's
> credential becouse it's directly provided by Windows via the logins'ID
> (sid), which authenticate user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||I don't understand the following stement:
> database access, but a (database) user must be mapped to the corresponding
> login..
what is the difference between a Login and a User?
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40acn2F19erhvU1@.individual.net...
> the second authentication phase is at database level, where each login
> will
> be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on)
> privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order
> to access a specified database the simple login existance does not provide
> database access, but a (database) user must be mapped to the corresponding
> login.. and is about verifying that at each object level (including
> database, tables, views, columns, procedures and so on) the Login/User
> association is permitted access to...
> CREATE LOGIN http://msdn2.microsoft.com/en-us/library/ms189751.aspx
> CREATE USER http://msdn2.microsoft.com/en-us/library/ms173463.aspx
> GRANT {database permission}
> http://msdn2.microsoft.com/en-us/library/ms178569.aspx
> GRANT {permission} http://msdn2.microsoft.com/en-us/library/ms187965.aspx
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Andrew,
Andrew Chalk wrote:
> Andrea: Thanks you for your detailed reply.
> 1) I created a login under the server->security folder.
> I did not give this Login any Roles. It is just a plain ol' user
> (public).
it's not a user, it's a login and there's no "public" server role... it's
just a login which is not member of server roles...

> 2) I went to the server instance name at the top and GRANTed "Connect
> SQL" permission to the new Login just created;
yep... the login has been created, enabled, and granted access to the
instance..

> 3) I went to the database and granted "Connect" permission to the
> Login.
ok, you granted db access to the login, which is now member of th public
database role..

> After these steps I get the error reported in my earlier post.
I reproduced your steps and I can log on to SQLExpress as a standard SQL
Server Login, and access a db the login has been mapped to a corresponding
user.. I did it via SSMSExpress and not via ASP..

> I should add that I am not an SQL Server expert and first looked at
> the SQL Server Mgt. Studio Express BOL.They are absolutely useless.
:D
download the full BOL, december released..

> what is the difference between a Login and a User?
a Login is the very first part of the 2 phases security policy enforcement
of SQL Server... I repeat what I already wrote..
SQL Server uses a so called "2 phases" authentication policy:
first an SQL Server Login or a Windows login must be created in order to
grant access to the SQL Server instance... at the server level a login can
be made member of none, 1 or all of the fixed server roles, which include
"sysadmin" role and so on...
you can choose between 2 authentication modes:
WinNT (trusted) connections or SQL Server authenticated connections... the
latter always requires full user's credential such as "User
Id=sa;Password=pwd", the password can be NULL so it must not be specified,
but I strongly advise you always to ensure strong passwords are present...
WindowsNT authentication, on the contrary, does not requires credential
becouse it's directly provided by Windows via the logins' ID (sid), which
authenticate (Windows) user's login at the windows login step... SQL Server
only needs to verify that the corresponding login and/or group is granted to
log on the instance (checking the granted Login's existence in the SQL
Server instance)...
the second authentication phase is at database level, where each login will
be granted database access mapping to a database user... here access
permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so on)
privileges at an object level (or column level for tables and views)..
so, the second phase regards a database security implementation... in order
to access a specified database the simple login existance does not provide
database access, but a (database) user must be mapped to the corresponding
login.. and is about verifying that at each object level (including
database, tables, views, columns, procedures and so on) the Login/User
association is permitted access to...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
The phrase:
> the login has been mapped to a corresponding user..
Can you explain that.
How did it happen?
Who is the user?
Is it correct to say that the user is an instance of utilizing a login? I.e.
"users" have logins. Some may use the same logins as others (for example
several instances of an application). In which case each application trying
to use SQL server is a user and their name/password pair correspond to their
login?
Thanks.
|||I now have a role named 'fred' and a user named 'fred' of the database that
I want the application to access. Is the use of the same name the problem?
Thanks
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
> :D
> download the full BOL, december released..
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||OK, fixed it, though mainly through luck than knowhow.
Deleted user "fred" and login "fred".
Recreated login Fred but made sure that I went to 'User Mapping' and checked
the database that I wanted this Login to be able to access;
Login was created and, like magic, a user appeared, also named 'fred', under
the database.
I still don't understand this.
Nonetheless, thanks for your help,
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40d46vF19tm2gU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> it's not a user, it's a login and there's no "public" server role... it's
> just a login which is not member of server roles...
>
> yep... the login has been created, enabled, and granted access to the
> instance..
>
> ok, you granted db access to the login, which is now member of th public
> database role..
>
> I reproduced your steps and I can log on to SQLExpress as a standard SQL
> Server Login, and access a db the login has been mapped to a corresponding
> user.. I did it via SSMSExpress and not via ASP..
>
> :D
> download the full BOL, december released..
>
> a Login is the very first part of the 2 phases security policy enforcement
> of SQL Server... I repeat what I already wrote..
> SQL Server uses a so called "2 phases" authentication policy:
> first an SQL Server Login or a Windows login must be created in order to
> grant access to the SQL Server instance... at the server level a login can
> be made member of none, 1 or all of the fixed server roles, which include
> "sysadmin" role and so on...
> you can choose between 2 authentication modes:
> WinNT (trusted) connections or SQL Server authenticated connections... the
> latter always requires full user's credential such as "User
> Id=sa;Password=pwd", the password can be NULL so it must not be specified,
> but I strongly advise you always to ensure strong passwords are
> present...
> WindowsNT authentication, on the contrary, does not requires credential
> becouse it's directly provided by Windows via the logins' ID (sid), which
> authenticate (Windows) user's login at the windows login step... SQL
> Server only needs to verify that the corresponding login and/or group is
> granted to log on the instance (checking the granted Login's existence in
> the SQL Server instance)...
> the second authentication phase is at database level, where each login
> will be granted database access mapping to a database user... here access
> permissions are set, as granting user/role SELECT/DELETE/EXECUTE (and so
> on) privileges at an object level (or column level for tables and views)..
> so, the second phase regards a database security implementation... in
> order to access a specified database the simple login existance does not
> provide database access, but a (database) user must be mapped to the
> corresponding login.. and is about verifying that at each object level
> (including database, tables, views, columns, procedures and so on) the
> Login/User
> association is permitted access to...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
|||hi Andrew,
Andrew Chalk wrote:
> The phrase:
> Can you explain that.
> How did it happen?
> Who is the user?
perhaps my english is not as good as I thought.. :D
perhaps here,
http://msdn.microsoft.com/library/de...rity_4fol.asp,
the architectural design is more clear..
a SQL Server Login (both standard SQL Server login or a WinNT login) is just
the very first brick in the wall in order to access a SQL Server instance...
without that you can not log in and connect to a specified instance... it
has a server wide range...
a User is just a database user (can have the same name as the corresponding
Login [Login is now somehaw obsolete, to be replaced by principals] but this
is not mandatory even if it's recommended in order to minimize troubles :D)
that, at creation time, is mapped via it's sid column
(dbname.sys.sysusers.sid) to an existing login
(master.sys.server_principals.sid)
SELECT u.name AS [Name in DB], u.hasdbaccess ,
p.name AS [Login Name]
FROM master.sys.server_principals p JOIN sys.sysusers u
ON p.sid = u.sid
Principals
http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
DB Users
http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx

> Is it correct to say that the user is an instance of utilizing a
> login? I.e. "users" have logins.
Logins can be granted db access as database users, but they can even not..

>Some may use the same logins as
> others (for example several instances of an application). In which
> case each application trying to use SQL server is a user and their
> name/password pair correspond to their login?
an application is not a user or a login... in certain case an application
role can represent this scenario
(http://msdn2.microsoft.com/en-us/library/ms190998.aspx)

> Deleted user "fred" and login "fred".
> Recreated login Fred but made sure that I went to 'User Mapping' and
> checked the database that I wanted this Login to be able to access;
> Login was created and, like magic, a user appeared, also named
> 'fred', under the database.
it's correct... you created a login... then you granted that login db access
via a database user (in the defined database) mapping it to the original
login ...
if you only create a login without grating him/her access to your desired
database(s), that login will only access it if the predefined "guest"
database user is available (and usually that db user is not available for
security reason)...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||That URL does not use the work 'Login', only 'User'.
What is the difference between a login and a user?
Thanks,
Andrew
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:40dk3lF198jigU1@.individual.net...
> hi Andrew,
> Andrew Chalk wrote:
> perhaps my english is not as good as I thought.. :D
> perhaps here,
> http://msdn.microsoft.com/library/de...rity_4fol.asp,
> the architectural design is more clear..
> a SQL Server Login (both standard SQL Server login or a WinNT login) is
> just the very first brick in the wall in order to access a SQL Server
> instance... without that you can not log in and connect to a specified
> instance... it has a server wide range...
> a User is just a database user (can have the same name as the
> corresponding Login [Login is now somehaw obsolete, to be replaced by
> principals] but this is not mandatory even if it's recommended in order to
> minimize troubles :D) that, at creation time, is mapped via it's sid
> column (dbname.sys.sysusers.sid) to an existing login
> (master.sys.server_principals.sid)
> SELECT u.name AS [Name in DB], u.hasdbaccess ,
> p.name AS [Login Name]
> FROM master.sys.server_principals p JOIN sys.sysusers u
> ON p.sid = u.sid
> Principals
> http://msdn2.microsoft.com/en-us/library/ms181127(en-US,SQL.90).aspx
> DB Users
> http://msdn2.microsoft.com/en-us/library/ms190928(en-US,SQL.90).aspx
>
> Logins can be granted db access as database users, but they can even not..
>
> an application is not a user or a login... in certain case an application
> role can represent this scenario
> (http://msdn2.microsoft.com/en-us/library/ms190998.aspx)
>
> it's correct... you created a login... then you granted that login db
> access via a database user (in the defined database) mapping it to the
> original login ...
> if you only create a login without grating him/her access to your desired
> database(s), that login will only access it if the predefined "guest"
> database user is available (and usually that db user is not available for
> security reason)...
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.16.0 - DbaMgr ver 0.61.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>
sql

Creating a User in Sql....

By Default the sql have the user Sa...
I want to create a user with permissons to create a
database...
How can i do it?
If you have some code sample i would apreciate a lot!
Thanks in advance> By Default the sql have the user Sa...

quote:

---

> I want to create a user with permissons to create a
> database...
> How can i do it?
> If you have some code sample i would apreciate a lot!

---

You should check and learn about the security as soon as possible. Using sa
is a very bad practice. Start with "Managing Security" module in Books
OnLine
(mk:@.MSITStore:C:\Program%20Files\Micros
oft%20SQL%20Server\80\Tools\Books\ad
minsql.chm::/ad_security_05bt.htm). You will find out that you need to
create a login (use Windows logins, if it is possible) and put the login in
the dbcreator fixed server role.
Dejan Sarka, SQL Server MVP
Please reply only to the newsgroups.

Creating a User Help!

I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
Thanks
Try:
Dim oUser as New SQLDMO.User
oUser.Login = "JoeUser"
oDatabase.Users.Add (oUser)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Nando_uy" <Nandouy@.discussions.microsoft.com> wrote in message
news:DBA816C1-62C5-46D2-9BA6-2DF40862D6E9@.microsoft.com...
I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
Thanks

Creating a User Help!

I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
ThanksTry:
Dim oUser as New SQLDMO.User
oUser.Login = "JoeUser"
oDatabase.Users.Add (oUser)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Nando_uy" <Nandouy@.discussions.microsoft.com> wrote in message
news:DBA816C1-62C5-46D2-9BA6-2DF40862D6E9@.microsoft.com...
I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
Thanks

Creating a User Help!

I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
ThanksTry:
Dim oUser as New SQLDMO.User
oUser.Login = "JoeUser"
oDatabase.Users.Add (oUser)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Nando_uy" <Nandouy@.discussions.microsoft.com> wrote in message
news:DBA816C1-62C5-46D2-9BA6-2DF40862D6E9@.microsoft.com...
I want to create a New user using SqlDmo through a VB 6.0
Any Idea?
Anybody can send me a code snippet?
Thanks

Creating a user for replication

Anyone have the steps (permissions and etc...) to create a user for use only in replications instead use "sa" login ?we have created a dedicated login for the Replication with sa rights|||but the user need to have sa role ?!|||Yes it needs to have the sa Rights..sql

Creating a User Defined Aggregate Function

Does SQL Server allow the user to create user defined aggregate functions?
If so:
What doed the syntax look like?
Where can I find more information on creating user defined aggregate
functions?Sorry, I found the answer.
"Charles" wrote:

> Does SQL Server allow the user to create user defined aggregate functions?
> If so:
> What doed the syntax look like?
> Where can I find more information on creating user defined aggregate
> functions?
>|||In SQL 2005 user-defined functions can be created as CLR functions.
http://msdn2.microsoft.com/en-us/library/ms131051.aspx
ML
http://milambda.blogspot.com/
"Charles" wrote:

> Does SQL Server allow the user to create user defined aggregate functions?
> If so:
> What doed the syntax look like?
> Where can I find more information on creating user defined aggregate
> functions?
>

Creating a user

Hi,
I need to give access to my collegue to access my database(say Sales),we are both in same network domain,i tried it but all in vain.
thnx in advance.
sudheerRefer to books online for SP_GRANTDBACCESS & SP_GRANTLOGIN topic.

Creating a User

Hi,
I am having a problem while creating an user to a DB.
When I Click users --> New Database User --> it opens an New user
creation window. Under the login name the -drop down I have this User
"user_ABC" listed. (I don't know how this user ended up in this drop
down). I select this login name and keep the deafult user name -->
which is also "user_ABC" , give appropriate rights and click OK. When
I refresh the users list, this user is listed in the list. Allthough
when the user tries to access the database, he gets an error message
as "permission denied" .
To solve this problem, I deleted user "user_ABC" from the users list.
Refresh the list the user "user_ABC" is not present. Try to create a
new user, the deleted user is still exist in the Login Name: drop down
list.
Can anybody please help me understand what is going wrong in creating
a user?
Thanks in Adv.
Sunitadeleting a user from a database does not delete the login.
the login is what appears in the drop down you talk about.
go to security->logins and try managing the user account from there.
Sunita wrote:
> Hi,
> I am having a problem while creating an user to a DB.
> When I Click users --> New Database User --> it opens an New user
> creation window. Under the login name the -drop down I have this User
> "user_ABC" listed. (I don't know how this user ended up in this drop
> down). I select this login name and keep the deafult user name -->
> which is also "user_ABC" , give appropriate rights and click OK. When
> I refresh the users list, this user is listed in the list. Allthough
> when the user tries to access the database, he gets an error message
> as "permission denied" .
> To solve this problem, I deleted user "user_ABC" from the users list.
> Refresh the list the user "user_ABC" is not present. Try to create a
> new user, the deleted user is still exist in the Login Name: drop down
> list.
> Can anybody please help me understand what is going wrong in creating
> a user?
> Thanks in Adv.
> Sunita|||Thanks, it solved my problem.
Sunita
chxxx <chxxx@.dontemailme.com> wrote in message news:<3FB28F16.36350FA8@.dontemailme.com>...
> deleting a user from a database does not delete the login.
> the login is what appears in the drop down you talk about.
> go to security->logins and try managing the user account from there.
>
> Sunita wrote:
> > Hi,
> >
> > I am having a problem while creating an user to a DB.
> >
> > When I Click users --> New Database User --> it opens an New user
> > creation window. Under the login name the -drop down I have this User
> > "user_ABC" listed. (I don't know how this user ended up in this drop
> > down). I select this login name and keep the deafult user name -->
> > which is also "user_ABC" , give appropriate rights and click OK. When
> > I refresh the users list, this user is listed in the list. Allthough
> > when the user tries to access the database, he gets an error message
> > as "permission denied" .
> >
> > To solve this problem, I deleted user "user_ABC" from the users list.
> > Refresh the list the user "user_ABC" is not present. Try to create a
> > new user, the deleted user is still exist in the Login Name: drop down
> > list.
> >
> > Can anybody please help me understand what is going wrong in creating
> > a user?
> >
> > Thanks in Adv.
> > Sunita|||Try setting up the user from Security > Logins rather than
at the database level. You should probably delete the
user from the database first if he is still there and
reset the password at the login level. In Security >
Logins you can select which database the user should have
access to. When you go back to that database, he should
be there. You can set up permissions.
>--Original Message--
>Hi,
>I am having a problem while creating an user to a DB.
>When I Click users --> New Database User --> it opens an
New user
>creation window. Under the login name the -drop down I
have this User
>"user_ABC" listed. (I don't know how this user ended up
in this drop
>down). I select this login name and keep the deafult user
name -->
>which is also "user_ABC" , give appropriate rights and
click OK. When
>I refresh the users list, this user is listed in the
list. Allthough
>when the user tries to access the database, he gets an
error message
>as "permission denied" .
>To solve this problem, I deleted user "user_ABC" from the
users list.
>Refresh the list the user "user_ABC" is not present. Try
to create a
>new user, the deleted user is still exist in the Login
Name: drop down
>list.
>Can anybody please help me understand what is going wrong
in creating
>a user?
>Thanks in Adv.
>Sunita
>.
>

Creating a Trigger on Table Access

Hi,
I am trying to create a trigger to update a datetime field when a user
logs in to their account. Is there a way to create a trigger that
updates a field when the table is accessed? The only other possible
way I can think of to accomplish this would be to write code that
updates a field on submit so that it trips the trigger I have to update
the time. This does not seem especially efficient, though.Hi
This is not possible through triggers, if you use a Stored procedure to
access the table you can add the code there. Doing this sort of thing may
incur a high performance penalty.
John
"iamalex84@.gmail.com" wrote:
> Hi,
> I am trying to create a trigger to update a datetime field when a user
> logs in to their account. Is there a way to create a trigger that
> updates a field when the table is accessed? The only other possible
> way I can think of to accomplish this would be to write code that
> updates a field on submit so that it trips the trigger I have to update
> the time. This does not seem especially efficient, though.
>|||Do you mean using updates to cause a trigger to trigger or using stored
procedures? Would incur a high performance penalty, that is.|||Hi Alex
For every time you did a select from your table, there would be a subsequent
update of another table. If there was a reasonable load this may result in a
bottleneck and therefore reduce performance. This would be true of any
auditing system regardless of whether you are auditing select, insert, update
or delete statements through triggers or code. In general most systems quite
often do a significantly larger number of selects than other statements
therefore the impact of auditing select statements would be higher. The only
way will you really know the impact is to benchmark your system under heavy
load and volumes.
John
"Alex" wrote:
> Do you mean using updates to cause a trigger to trigger or using stored
> procedures? Would incur a high performance penalty, that is.
>

Creating a table from data in a SQL server db

I have a form with a drop down box so the user can select a quote.. When a quote is selected i need to populate a table of all the records associated with the quote id. I need the table to be created in such a way that the user can add new rows, delete rows and edit the data. Then all of the changes need to be written back to the database. Whats the most efficient/best way of doing this and if you have any ideas can you explain them as thoroughly as possible! I'm currently upgrading an access database to a sql server back end with an asp.net client and it's taking me a while to get to grips with all the changes!
Thanks in advance,
Chrisyou could create a dataset/datatable at the front end with the samestructure as the actual table in the db. You can then use thedataset/datatable and do all the modifications and push the entiretable back to the sql db. check out articles about datasets and youwould find enough info.
sql

creating a table column that only takes data from another table.

I am trying to create a table that holds info about a user; with the usual columns for firstName, lastName, etc... no problem creating the table or it's columns, but how can I "restrict" the values of my State column in the 'users' table so that it only accepts values from the 'states' table?

You could create a trigger on the table or a rule?|||The term which applies to your situation is called "referential integrity". To enforce referential integrity in your situation, you could use aFOREIGN KEY constraint in your table which limits the possibilities in the State column to only those values in the 'states' table.sql

creating a new user for sp execution say sa (to whom i am in need of creating under my user defi

I am in need of creating a new user for stored procedures execution say sa (to whom i am in need of creating under my user defined db)

I am guessing you are trying to create an explicit user (database principal) for the login SA, correct?

SA and any other member of sysadmin will always be mapped on any database to DBO, even more, SA is a special principal and the DDL will prevent to create a user for it.

If you need this principal for EXECUTE AS in modules, I would recommend either creating a user without login or use digital signatures. You can find some samples on my blog and Laurentiu’s blog:

· http://blogs.msdn.com/raulga/

· http://blogs.msdn.com/lcris/

Hopefully this information will help, but if you still have any problems let us know.

-Raul Garcia

SDE/T

SQL Server Engine

Creating a new login with limited rights

what combonation do you use to create a user with rights to add alertsand to
create backup jobs. This user should be able to modify databases or other u
sers.Hi,
create backup jobs - Any one with public role can create the job
Create Alerts - Only members of sysadmin fixed server role can create the
alerts
Modifying users - Provide security admin server fixed role
Modify databases - Disk admin server fixed role
Note:
You have to do all the admin functions using this user, Preferably you can
assign 'Sysadmn' fixed server role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:232E3D1A-E424-4DC7-AD3B-6C97DF2947A9@.microsoft.com...
> what combonation do you use to create a user with rights to add alertsand
to create backup jobs. This user should be able to modify databases or other
users.|||-- Hari wrote: --
Hi,
create backup jobs - Any one with public role can create the job
Create Alerts - Only members of sysadmin fixed server role can create the
alerts
Modifying users - Provide security admin server fixed role
Modify databases - Disk admin server fixed role
Note:
You have to do all the admin functions using this user, Preferably you can
assign 'Sysadmn' fixed server role.
Thanks
Hari
MCDBA
"robert" <rsalazar@.cbbank.com> wrote in message
news:232E3D1A-E424-4DC7-AD3B-6C97DF2947A9@.microsoft.com...
> what combonation do you use to create a user with rights to add alertsand
to create backup jobs. This user should be able to modify databases or other
users.
Bummer, Since I'm new at this my role as DBA needs to be limited until I lea
rn more. What is the posibility of creating a user on our live server that c
an creat backup jobs, assign notification in the jobs created, and to view t
he event long on the s
erver itself, not the SQL log. Also, I want to limit the ability to change a
ny settings as far as the databases are concerned.
My login I created contains the following.
No server roles are selected
All databases are selected
Public-is checked
db_securityadmin is checked
db_backupoperator is checked

creating a new login and user

hi,
I have a database : 'harshal'
and want to create a login/user called : 'thisuser'
and want to make him the db_owner for the database.
I am using the following script .

if exists (select * from master.dbo.syslogins where loginname=N'thisuser')
exec sp_droplogin 'thisuser'
if not exists (select * from master.dbo.syslogins where loginname = N'thisuser')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132)
select @.logindb = N'harshal', @.loginlang = N'us_english'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'thisuser', null, @.logindb, @.loginlang
if not exists (select * from dbo.sysusers where name = N'thisuser' and uid < 16382)
EXEC sp_grantdbaccess N'thisuser', N'harshal'
exec sp_defaultdb 'thisuser','harshal'
exec sp_addrolemember 'thisuser','db_owner'
exec SP_ADDUSER 'thisuser','harshal'
end

but it gives me the following error:

New login created.
Granted database access to 'thisuser'.
Default database changed.
Server: Msg 15014, Level 16, State 1, Procedure sp_addrolemember, Line 37
The role 'thisuser' does not exist in the current database.
Server: Msg 15023, Level 16, State 1, Procedure sp_grantdbaccess, Line 126
User or role 'harshal' already exists in the current database.

any help would be greatly appreciated.
regards,
harhsal.exec [sp_addrolemember] 'db_owner','thisuser':)|||how could I miss that one :(
thanks anyways.
regards,
Harshal.|||looks like u r new to these sp ... can check in online book.

correct code is as following ...

-------
if exists (select * from master.dbo.syslogins where loginname=N'thisuser')
exec sp_droplogin 'thisuser'
if not exists (select * from master.dbo.syslogins where loginname = N'thisuser')
BEGIN
declare @.logindb nvarchar(132), @.loginlang nvarchar(132)
select @.logindb = N'harshal', @.loginlang = N'us_english'
if @.logindb is null or not exists (select * from master.dbo.sysdatabases where name = @.logindb)
select @.logindb = N'master'
if @.loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @.loginlang) and @.loginlang <> N'us_english')
select @.loginlang = @.@.language
exec sp_addlogin N'thisuser', null, @.logindb, @.loginlang
if not exists (select * from dbo.sysusers where name = N'thisuser' and uid < 16382)
EXEC sp_grantdbaccess 'thisuser', 'harshal'
exec sp_addrolemember 'db_owner','thisuser'
end

-------