Minium Permissions necessary to create a login?

What are the minimum permissions necessary to use CREATE LOGIN?

I'm using db_accessAdmin and db_securityAdmin and db_owner. The user as access to the database.

It still says I can't create the login. What am I missing?

The answer is in Books Online:

http://msdn2.microsoft.com/en-us/library/ms189751.aspx

"Requires ALTER ANY LOGIN permission on the server. If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server."

Creating a login requires permissions at the server level, it cannot be accomplished with database level permissions.

Thanks
Laurentiu

sql

Minium Permissions necessary to create a login?

What are the minimum permissions necessary to use CREATE LOGIN?

I'm using db_accessAdmin and db_securityAdmin and db_owner. The user as access to the database.

It still says I can't create the login. What am I missing?

The answer is in Books Online:

http://msdn2.microsoft.com/en-us/library/ms189751.aspx

"Requires ALTER ANY LOGIN permission on the server. If the CREDENTIAL option is used, also requires ALTER ANY CREDENTIAL permission on the server."

Creating a login requires permissions at the server level, it cannot be accomplished with database level permissions.

Thanks
Laurentiu

Minimum SQL Server permissions needed

I have some Access .adp VBA code (below) that deletes a SQL Server 2005 tabl
e
and recreates it.
DoCmd.DeleteObject acTable, "dbo.tblTemp"
DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
I would like to create a role that has the minimum permissions necessary to
do this. Any advice?
If I don't SELECT control to the two tables, Access doesn't seem to see
them. If I don't grant CONTROL, Access doesn't seem to be able to drop the
tables. Yet, after the tblTemp is recreated in the CopyObject line of code,
Access doesn't see it again as the role doesn't have that SELECT or CONTROL
permissions granted to it anymore.
Another problem which seems like overkill was that I had to grant CREATE
TABLE to the role.
Also, If I don't grant control to the role dbo, Access squawks about not
having permissions to dbo.
Your help is appreciated.Not sure about your case - as you don't mention how you are trying to access
these tables after their creations but did you look into the possibility of
creating your tables in the tempdb database?
(I don't even know if this will work from DoCmd.*; however, using a SP or
the ADO connection or the command objects would probably be a much better
idea than using DoCmd.* even if the DoCmd.* are working).
For example:
Set NoCount ON
create table Tempdb..Members (IdMember int Identity (1,1) primary key,
firstname
varchar(50) collate database_default)
insert into Tempdb..Members (firstname) values ('deny')
insert into Tempdb..Members (firstname) values ('ben')
select M.* from Tempdb..Members as M
drop table Tempdb..Members
(the collate database_default statement is there in case the default
collation for the tempdb database would be different from the default
collation of the current database. If this your case, don't forget the
collate database_default statement and don't use a use Tempdb
statement; otherwise the collation used will be the one defined for the
tempdb database. Of course, if both default collations are the same then
you don't have to fiddle with this.)
In my opinion, granting Control and Create table permission on an account is
pretty much giving away all security; so if possible, it would be a much
better idea to use the tempdb database; as this database has been created
exactly for that purpose.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>I have some Access .adp VBA code (below) that deletes a SQL Server 2005
>table
> and recreates it.
> DoCmd.DeleteObject acTable, "dbo.tblTemp"
> DoCmd.CopyObject , "tblTemp", acTable, "dbo.tblTempStructure"
> I would like to create a role that has the minimum permissions necessary
> to
> do this. Any advice?
> If I don't SELECT control to the two tables, Access doesn't seem to see
> them. If I don't grant CONTROL, Access doesn't seem to be able to drop
> the
> tables. Yet, after the tblTemp is recreated in the CopyObject line of
> code,
> Access doesn't see it again as the role doesn't have that SELECT or
> CONTROL
> permissions granted to it anymore.
> Another problem which seems like overkill was that I had to grant CREATE
> TABLE to the role.
> Also, If I don't grant control to the role dbo, Access squawks about not
> having permissions to dbo.
> Your help is appreciated.|||For the connection object, the most simple is to use the one who is already
available:
CurrentProject.Connection.Execute ("Your sql statement here")
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:%23aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>|||Sylvain,
This is an older app that I've inherited. It currently runs with sa rights
(not good), so I was trying to create a new user & role with a limited set o
f
rights that could still allow the Access app to do what it needs. I was
hoping to avoid overly changing the MS Access code, but that may not be
possible. Your suggestion of ditching the DoCmd for executing a stored proc
though is a good one. I'll pursue this.
Thank you
"Sylvain Lafontaine" wrote:
[vbcol=seagreen]
> Not sure about your case - as you don't mention how you are trying to acce
ss
> these tables after their creations but did you look into the possibility o
f
> creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the ? collate database_default ? statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the ?
> collate database_default ? statement and don't use a ? use Tempdb ?
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
is
> pretty much giving away all security; so if possible, it would be a much
> better idea to use the tempdb database; as this database has been created
> exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...|||Rob (Rob@.discussions.microsoft.com) writes:
> This is an older app that I've inherited. It currently runs with sa
> rights (not good), so I was trying to create a new user & role with a
> limited set of rights that could still allow the Access app to do what
> it needs. I was hoping to avoid overly changing the MS Access code, but
> that may not be possible. Your suggestion of ditching the DoCmd for
> executing a stored proc though is a good one. I'll pursue this.
Yes, putting this in a stored proceedure is the only way out. But Sylvain
did not tell the full story. For this to work you need to sign the procedure
with a certificate, and create a user fot the certificate and grant that
user the required rights.
I describe this in detail in this article on my web site:
http://www.sommarskog.se/grantperm.html
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|||"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> ha
scritto nel messaggio news:#aSMFFaPIHA.5184@.TK2MSFTNGP05.phx.gbl...
> Not sure about your case - as you don't mention how you are trying to
> access these tables after their creations but did you look into the
> possibility of creating your tables in the tempdb database?
> (I don't even know if this will work from DoCmd.*; however, using a SP or
> the ADO connection or the command objects would probably be a much better
> idea than using DoCmd.* even if the DoCmd.* are working).
> For example:
> Set NoCount ON
> create table Tempdb..Members (IdMember int Identity (1,1) primary key,
> firstname
> varchar(50) collate database_default)
> insert into Tempdb..Members (firstname) values ('deny')
> insert into Tempdb..Members (firstname) values ('ben')
> select M.* from Tempdb..Members as M
> drop table Tempdb..Members
>
> (the collate database_default statement is there in case the default
> collation for the tempdb database would be different from the default
> collation of the current database. If this your case, don't forget the
> collate database_default statement and don't use a use Tempdb
> statement; otherwise the collation used will be the one defined for the
> tempdb database. Of course, if both default collations are the same then
> you don't have to fiddle with this.)
> In my opinion, granting Control and Create table permission on an account
> is pretty much giving away all security; so if possible, it would be a
> much better idea to use the tempdb database; as this database has been
> created exactly for that purpose.
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:56B5B829-9DE2-4805-9F9C-076E49F6ABEF@.microsoft.com...
>

minimum role/permissions for backup

I have a MS SQL database process that is run from ASP.NET.
I would like to be able to backup the SQL database using either
a full database or transaction log backup immediately before the
process is done. Is this possible with ASP.NET and
what kind of permissions or role would the SQL connection account
need to perfom the backup. I would like to give the account the minimum
permissions needed.
Ed
--
EdThere is a built in SQL Server role called "db_backupoperator".
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:6EF64E64-B72C-4C84-9AD8-1F9B3072758A@.microsoft.com...
>I have a MS SQL database process that is run from ASP.NET.
> I would like to be able to backup the SQL database using either
> a full database or transaction log backup immediately before the
> process is done. Is this possible with ASP.NET and
> what kind of permissions or role would the SQL connection account
> need to perfom the backup. I would like to give the account the minimum
> permissions needed.
> --
> Ed
> --
> Ed

Minimum permissions for SQL2005 development?

Hi
I'm trying to get started developing on this 2005 box, but I'm running
into big security problems, because this is one of those shops where
getting the most minimal level of access to the dev server (even as a
developer who should supposedly be doing something productive for the
daily rate) is harder than getting Tony Blair to tell you the Trident
launch codes.
So at the moment even Management Studio won't work. And the security
administrators are not SQL DBAs - so it's basically up to me to work
out what access I need (db_owner/sa? For a developer, on the dev
box? Forget it!), tell them this, and then go through whatever
paperwork/string-pulling/bribery/black ops are necessary to actually
get it granted.
If it was 2000 I'd have some idea, but I'm relatively new to 2005 -
developed on a "friendly" box as sa before, but never run into this.
Anyone know what permissions I'd need to:
- Create/drop/execute objects in a DB (obviously, db_datawriter and
db_datareader as well)
- Execute system stored procedures (especially sp_executesql)
- Develop, test and run SS Integration Services?
I suspect that what I'm running into at the moment is lack of
permissions in master and msdb - but it's hard to work out, when my
effective server role is something like "Unauthenticated hacker".
Apologies for my ignorance - I've never run into this level of
paranoia before.
thanks for any ideas.
seb> Minimum permissions for SQL2005 development?
The obvious answer is that you need the minimal level of permissions need to
do your job :-)

> And the security
> administrators are not SQL DBAs - so it's basically up to me to work
> out what access I need (db_owner/sa?
One would think the security admins would need to know something about SQL
security if managing SQL security is part of their job. You should only
need to tell them what you need to do and they ought to be able to know
enough to give you those permissions and nothing more.
Sysadmin role members have full control over the entire SQL Server and will
provide full control over all databases. If you don't need to change server
configuration options, install service packs and that sort of thing,
sysadmin is probably overkill. db_owner is also a powerful role but may be
appropriate if you need *complete* control over some, but not all, databases
on the server.

> Anyone know what permissions I'd need to:
> - Create/drop/execute objects in a DB (obviously, db_datawriter and
> db_datareader as well)
You'll need CREATE permissions on the target object type and ALTER on the
object's schema. If you have CONTROL on the schema, you won't need
db_datawriter and db_datareader membership to data access in that schema.

> - Execute system stored procedures (especially sp_executesql)
You should already have execute permissions on sp_executesql because execute
permissions default to public and you access master via the guest user
context. If you don't have execute permissions, the default SQL Server
permissions were changed and permissions will need to be granted via other
means.

> - Develop, test and run SS Integration Services?
Like any application, you'll SQL permissions to access the objects used by
the package. If you use msdb as the package store, you'll need to be a
member of the appropriate role (db_dtsadmin or db_dtsltduser).
Hope this helps.
Dan Guzman
SQL Server MVP
"sebt" <sebthirlway@.hotmail.com> wrote in message
news:1175679014.583549.224630@.b75g2000hsg.googlegroups.com...
> Hi
> I'm trying to get started developing on this 2005 box, but I'm running
> into big security problems, because this is one of those shops where
> getting the most minimal level of access to the dev server (even as a
> developer who should supposedly be doing something productive for the
> daily rate) is harder than getting Tony Blair to tell you the Trident
> launch codes.
> So at the moment even Management Studio won't work. And the security
> administrators are not SQL DBAs - so it's basically up to me to work
> out what access I need (db_owner/sa? For a developer, on the dev
> box? Forget it!), tell them this, and then go through whatever
> paperwork/string-pulling/bribery/black ops are necessary to actually
> get it granted.
> If it was 2000 I'd have some idea, but I'm relatively new to 2005 -
> developed on a "friendly" box as sa before, but never run into this.
> Anyone know what permissions I'd need to:
> - Create/drop/execute objects in a DB (obviously, db_datawriter and
> db_datareader as well)
> - Execute system stored procedures (especially sp_executesql)
> - Develop, test and run SS Integration Services?
> I suspect that what I'm running into at the moment is lack of
> permissions in master and msdb - but it's hard to work out, when my
> effective server role is something like "Unauthenticated hacker".
> Apologies for my ignorance - I've never run into this level of
> paranoia before.
> thanks for any ideas.
>
> seb
>|||Hello Seb,
You need minimum level permission of db_ddladmin fixed database role so that
you will able to create objects in respective database, and always create
objects as DBO so that it won't result into broken ownership chain.
Goodluck!
Cheers,
MB
"sebt" <sebthirlway@.hotmail.com> wrote in message
news:1175679014.583549.224630@.b75g2000hsg.googlegroups.com...
> Hi
> I'm trying to get started developing on this 2005 box, but I'm running
> into big security problems, because this is one of those shops where
> getting the most minimal level of access to the dev server (even as a
> developer who should supposedly be doing something productive for the
> daily rate) is harder than getting Tony Blair to tell you the Trident
> launch codes.
> So at the moment even Management Studio won't work. And the security
> administrators are not SQL DBAs - so it's basically up to me to work
> out what access I need (db_owner/sa? For a developer, on the dev
> box? Forget it!), tell them this, and then go through whatever
> paperwork/string-pulling/bribery/black ops are necessary to actually
> get it granted.
> If it was 2000 I'd have some idea, but I'm relatively new to 2005 -
> developed on a "friendly" box as sa before, but never run into this.
> Anyone know what permissions I'd need to:
> - Create/drop/execute objects in a DB (obviously, db_datawriter and
> db_datareader as well)
> - Execute system stored procedures (especially sp_executesql)
> - Develop, test and run SS Integration Services?
> I suspect that what I'm running into at the moment is lack of
> permissions in master and msdb - but it's hard to work out, when my
> effective server role is something like "Unauthenticated hacker".
> Apologies for my ignorance - I've never run into this level of
> paranoia before.
> thanks for any ideas.
>
> seb
>

Minimum permission to attach / detach databases

Hi group,
I've got a problem determining permissions for users. I got one group
of users which only should have the abilitiy to attach / detach any
database to the SQL Server.
I just thought the SQL server wide user role dbcreator seems to be
sufficient for them but if I try to attach a database on which this
group does not have any permission within (its a database from an
external vendor), attaching the database fails.
Any hints on this?
Thanks,
HeikoHeiko
Can you elaborate a little bit?
>but if I try to attach a database on which this
> group does not have any permission within (its a database from an
> external vendor), attaching the database fails.
>
"Heiko Schuler" <hschuler@.gmail.com> wrote in message
news:1192693343.587584.291710@.v29g2000prd.googlegroups.com...
> Hi group,
> I've got a problem determining permissions for users. I got one group
> of users which only should have the abilitiy to attach / detach any
> database to the SQL Server.
> I just thought the SQL server wide user role dbcreator seems to be
> sufficient for them but if I try to attach a database on which this
> group does not have any permission within (its a database from an
> external vendor), attaching the database fails.
>
> Any hints on this?
>
> Thanks,
>
> Heiko
>|||> Can you elaborate a little bit?
Sure - I got one user which has the database role "dbcreator". When I
now try to attach a database, I got an error. the strange thing is - I
got 2 different depending using the GUI or T-SQL.
When I use the GUI, after selecting the mdf file, I got the following
error:
"User 'guest' does not have permission to run DBCC checkprimeryfile.
OK, this error message itself is clear because only members of the
sysadmin or server admin role can run DBCC commands. But why does the
GUI wants to run DBCC checkprimeryfile?
When I try to attach the database via T-SQL with sp_attach_db in Query
Analyzer, I got the following error:
"Unable to open the physical fiel <path to the mdf>. Operating system
error 5 (error not found).
Depending on the message error message above I first thougt it was a
permisson error on the file system. But if I put the user in the
sysadmin server role - everything works fine. So NTFS permission on
the file system is OK.
Heiko

Minimum NTFS Permissions required for SQL Server Reporting Services to run

I posted elsewhere my troubles in installing SQL Server Reporting services
on my development server. This server is locked down in (almost) the exact
same way as our production server in order to prove the ability of
applications to run and to protect our software under development.
I used two documents from Microsoft to set the NTFS Permissions:
Minimum NTFS Permissions Required for IIS 5.0 To work:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271071
and Aspnet_wp.exe Could Not Be Started Error Message When You View an
ASP.Net Page:
http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B811320
I was not able to install SQL Server Reporting Services when the NTFS
permissions were set as documented. I was forced to make the ASPNet user an
administrator to install, this proves that the issue is permission related.
I have not been able to find a document outlining the permissions needed for
SQL Server Reporting Services. Is anyone aware of one? Our servers must be
as secure as is reasonably possible so I need this information.
Thank you.Hmm.Were you trying to install RS while logged in as ASPnet user? As is not
atypical, when installing a server application you need to be logged in as
someone with Administrator priveleges on the server. However, this does not
mean that the accounts that run need to be in the admin group. When I
install it I log in as log administrator and install it. There is no reason
for aspnet user to be adminstrator (it isn't in my configuration).
Bruce L-C
"Roger Twomey" <rogerdev@.vnet.on.ca> wrote in message
news:%237ydtdTWEHA.1048@.tk2msftngp13.phx.gbl...
> I posted elsewhere my troubles in installing SQL Server Reporting services
> on my development server. This server is locked down in (almost) the exact
> same way as our production server in order to prove the ability of
> applications to run and to protect our software under development.
> I used two documents from Microsoft to set the NTFS Permissions:
> Minimum NTFS Permissions Required for IIS 5.0 To work:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271071
> and Aspnet_wp.exe Could Not Be Started Error Message When You View an
> ASP.Net Page:
> http://support.microsoft.com/default.aspx?scid=kb%3Ben-us%3B811320
> I was not able to install SQL Server Reporting Services when the NTFS
> permissions were set as documented. I was forced to make the ASPNet user
an
> administrator to install, this proves that the issue is permission
related.
> I have not been able to find a document outlining the permissions needed
for
> SQL Server Reporting Services. Is anyone aware of one? Our servers must be
> as secure as is reasonably possible so I need this information.
> Thank you.
>

Migration Of MSSQL instance along with security permissions

Hi All,

This is the scenario. Basically i work for a web hosting company now we have taken over control of a competitor and would like to migrate there existing data to our own MSSQL server's in order to integrate them into our automatic system etc etc ...

The current system is running MSSQL7.

The system been copied to is MSSQL2K.

Objectives:

Retain all security accounts listed on the MSSQL7 system and use them on the MSSQL2K system. This includes all the logins that use standard MSSQL login's. (Reason : To ensure that all the customers ASP pages work after the DB's have been copied.)
Backup all the databases on the MSSQL7 and restore them to the MSSQL2K system. Unfortunately system is running on a different network with firewall's etc in between. What type of maintenance plan and how should i implement the requirement to keep all the login account's?
Any help greatly appreciated......
thxone of the option can be like take all backups on either hdd or tape then connect it to ur server copy it from previous one to new one.
to copy all login info etc u can write a script if u need the script i can send u next time (this will take the info to a cursor and one by one it wil update.)
as far as sql 7 database schema and 2k schema is concerned i dont think it will be a problem.
this is an intersting case pl update me|||Hi ranjan apologies for not replying sooner (did not know if anyone had replied). I agree when u state that u dont think either that the difference between porting to 2000 is going to cause an issue. I have restored all the databases now but am in need of a little help / inspiration on how to port over the login names. Perhaps you can help or have a script that performs this task. Regards the script u have for updating i presume its independent to the current logins that are on the SQL2K system at present. How is this done?