How to create a new SQL account that just has rights to a specific database?

Gurus,
You know how in SQL Enterprise Manager you click on Databases node and see
all the databases? And that the SA account has full access to all of these?
Well, if, from here on out, I wanted to create a database that beside the SA
account had a separate account that had public and DB owner rights on it,
how would I create such a SQL account? I guess what I am asking is how to
create new SQL accounts.
--
SpinHi
Check out the topic "Managing Security" in Books Online. To add a login to
your server see sp_grantlogin. To grant access to a database use
sp_grantdbaccess, to add users to a role see sp_addrolemember.
John
"Spin" wrote:
> Gurus,
> You know how in SQL Enterprise Manager you click on Databases node and see
> all the databases? And that the SA account has full access to all of these?
> Well, if, from here on out, I wanted to create a database that beside the SA
> account had a separate account that had public and DB owner rights on it,
> how would I create such a SQL account? I guess what I am asking is how to
> create new SQL accounts.
> --
> Spin
>
>|||Can this be done via the GUI?
--
Spin
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:7783F616-4217-43D6-845A-B111A1DA042C@.microsoft.com...
> Hi
> Check out the topic "Managing Security" in Books Online. To add a login to
> your server see sp_grantlogin. To grant access to a database use
> sp_grantdbaccess, to add users to a role see sp_addrolemember.
> John
> "Spin" wrote:
>> Gurus,
>> You know how in SQL Enterprise Manager you click on Databases node and
>> see
>> all the databases? And that the SA account has full access to all of
>> these?
>> Well, if, from here on out, I wanted to create a database that beside the
>> SA
>> account had a separate account that had public and DB owner rights on it,
>> how would I create such a SQL account? I guess what I am asking is how
>> to
>> create new SQL accounts.
>> --
>> Spin
>>|||You first need to create a login for the account and then define the
account in the database. The general steps (In Enterprise Manager) are:
1) Expand the nodes on the left hand side of the console until you find
your SQL Server instance (e.g. \\CompName\InstanceName)
2) Expand the node for the instance & you should see a sub-node called
"Security". Expand the "Security" node and you should see a "Logins"
sub-node
3) Right click on the "Logins" node and select "New Login..." After
doing this the "New Login" window will appear
4) In the "New Login" window type in the name of the server login,
select the authentication type and set the default database (you
shouldn't need to worry about the default language)
5) Inthe "New Login" window, click on the "Database Access" tab to
bring up a list of databases on the server
6) Check the "Permit" text box next to the database you want to give
access to. A name for the user (same as the server login defined
previously) will appear next to the database name...Generally no need
to change this
NOTE: By default the server login will have access to a database on the
server IF the database has the "guest" account in it. If there is no
guest account then you need to explicitly give access as I've detailed
just now.
7) You'll notice in the bottom of the window that there is a list of
database roles. Your user will be in the "Public" role by default.
Select whatever roles are necessary.
8) That should be about it. Click OK to complete the process.
After doing the above steps you will have a server login that people
can use. This will have access to the database(s) you granted access to
in Step (6) with an access level as per the permissions you granted in
Step (7).
Hope that helps a little.|||That's great. With those steps, I do not need to go through the process of
sp_grantlogin, sp_grantdbaccess, and sp_addrolemember I take it?
Spin
<nate.vu@.gmail.com> wrote in message
news:1146956260.908362.69190@.i40g2000cwc.googlegroups.com...
> You first need to create a login for the account and then define the
> account in the database. The general steps (In Enterprise Manager) are:
> 1) Expand the nodes on the left hand side of the console until you find
> your SQL Server instance (e.g. \\CompName\InstanceName)
> 2) Expand the node for the instance & you should see a sub-node called
> "Security". Expand the "Security" node and you should see a "Logins"
> sub-node
> 3) Right click on the "Logins" node and select "New Login..." After
> doing this the "New Login" window will appear
> 4) In the "New Login" window type in the name of the server login,
> select the authentication type and set the default database (you
> shouldn't need to worry about the default language)
> 5) Inthe "New Login" window, click on the "Database Access" tab to
> bring up a list of databases on the server
> 6) Check the "Permit" text box next to the database you want to give
> access to. A name for the user (same as the server login defined
> previously) will appear next to the database name...Generally no need
> to change this
> NOTE: By default the server login will have access to a database on the
> server IF the database has the "guest" account in it. If there is no
> guest account then you need to explicitly give access as I've detailed
> just now.
> 7) You'll notice in the bottom of the window that there is a list of
> database roles. Your user will be in the "Public" role by default.
> Select whatever roles are necessary.
> 8) That should be about it. Click OK to complete the process.
> After doing the above steps you will have a server login that people
> can use. This will have access to the database(s) you granted access to
> in Step (6) with an access level as per the permissions you granted in
> Step (7).
> Hope that helps a little.
>|||Yes, that's correct...The above steps are for the GUI (Enterprise
Manager if you're on SQL Server 2000). Calling the stored procs is the
equivalent way of doing it by using T-SQL statements and using
something like Query Analyzer to execute them.
Both methods will achieve the same results.
Hope that helps a little.|||Hi
If you need to do this regularly then it will be a lot quicker if you use a
script and query analyser. The section on managing security in Books Online
will give you background information as well as tell you the different ways
of carrying out these activities this includes how to use Enterprise Manager.
John
"Spin" wrote:
> That's great. With those steps, I do not need to go through the process of
> sp_grantlogin, sp_grantdbaccess, and sp_addrolemember I take it?
>
> --
> Spin
>
> <nate.vu@.gmail.com> wrote in message
> news:1146956260.908362.69190@.i40g2000cwc.googlegroups.com...
> > You first need to create a login for the account and then define the
> > account in the database. The general steps (In Enterprise Manager) are:
> >
> > 1) Expand the nodes on the left hand side of the console until you find
> > your SQL Server instance (e.g. \\CompName\InstanceName)
> >
> > 2) Expand the node for the instance & you should see a sub-node called
> > "Security". Expand the "Security" node and you should see a "Logins"
> > sub-node
> >
> > 3) Right click on the "Logins" node and select "New Login..." After
> > doing this the "New Login" window will appear
> >
> > 4) In the "New Login" window type in the name of the server login,
> > select the authentication type and set the default database (you
> > shouldn't need to worry about the default language)
> >
> > 5) Inthe "New Login" window, click on the "Database Access" tab to
> > bring up a list of databases on the server
> >
> > 6) Check the "Permit" text box next to the database you want to give
> > access to. A name for the user (same as the server login defined
> > previously) will appear next to the database name...Generally no need
> > to change this
> >
> > NOTE: By default the server login will have access to a database on the
> > server IF the database has the "guest" account in it. If there is no
> > guest account then you need to explicitly give access as I've detailed
> > just now.
> >
> > 7) You'll notice in the bottom of the window that there is a list of
> > database roles. Your user will be in the "Public" role by default.
> > Select whatever roles are necessary.
> >
> > 8) That should be about it. Click OK to complete the process.
> >
> > After doing the above steps you will have a server login that people
> > can use. This will have access to the database(s) you granted access to
> > in Step (6) with an access level as per the permissions you granted in
> > Step (7).
> >
> > Hope that helps a little.
> >
>
>