Create table permission problem on dbo

I have 2 logins on a sql server database. Both appear identical in their
permissions. They both have been assigned a role which has permission to
create tables on the dbo user. One user can create tables in the dbo but the
other can't. Neither have specific db_owner permission. Any ideas?On Oct 5, 1:19 pm, Stu P <S...@.discussions.microsoft.com> wrote:
> I have 2 logins on a sql server database. Both appear identical in their
> permissions. They both have been assigned a role which has permission to
> create tables on the dbo user. One user can create tables in the dbo but the
> other can't. Neither have specific db_owner permission. Any ideas?
compare the two using sp_helplogins 'loginname' to see if you can find
something
Thanks
VS|||I think it would be better to be sure of what permissions those logins have
using following commands first.
exec sp_helplogins
exec sp_helpsrvrolemember
--
Ekrem Ã?nsoy
"Stu P" <StuP@.discussions.microsoft.com> wrote in message
news:EE30C124-4EB4-48C1-8D98-3C94623FDCB5@.microsoft.com...
>I have 2 logins on a sql server database. Both appear identical in their
> permissions. They both have been assigned a role which has permission to
> create tables on the dbo user. One user can create tables in the dbo but
> the
> other can't. Neither have specific db_owner permission. Any ideas?

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 table permission

Hi,
Is there any query available to check the existence of 'CREATE TABLE' permission in a database
?
Please advice,
Thanks,
MiraJI really need to get my hands on an SQL Server ER Diagram...but start with syspermissions

CREATE Table permission

I gave a user explicit permission to create table, but still getting
this error:

Property Default Schema is not available for database[DBNAME]. This
property may not exist for this object or may not be recoverable due to
insufficient access right. Microsoft.SQLServer.Express.SQLEditors

Do I need to create a new schema?

--sharifSharif Islam (mislam@.npspam.uiuc.edu) writes:

Quote:

Originally Posted by

I gave a user explicit permission to create table, but still getting
this error:
>
Property Default Schema is not available for database[DBNAME]. This
property may not exist for this object or may not be recoverable due to
insufficient access right. Microsoft.SQLServer.Express.SQLEditors

Sounds like you are using some graphical tool in SSMS. Those tools are
of poor quality, and I recommend that you try CREATE TABLE instead.

But only CREATE TABLE may not be sufficient. The user may also need ALTER
permission on the schema (for instance dbo).

--
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 Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJo

Create Snapshot -> FAILED! you dont have sufficient permission to run this command

Hi all,

i'm trying to create a publication and its snapshot in the default snapshot folder of MS SQL Server 2005.
It's all done by RMO.

Following Scenario:
1. PublicationDB was created by User1(sysadmin) ... successful
2. Enable PublicationDB for Publishing ... successful

2. Creating the publication: executed as User2(db_owner)

2.1 publication.Create(); ... successful

2.2 publication.CreateSnapshotAgent(); ... successful
2.3 Add Articles to publication ... successful
2.4 Generate Snapshot with

agent = new SnapshotGenerationAgent(); and setting all parameters for it, then execute by

agent.GenerateSnapshot();

And at this point,i got an error message, because the snapshot agent cant be executed ...

2007-09-12 12:05:46.58 User-specified agent parameter values:
2007-09-12 12:05:46.58 --
2007-09-12 12:05:46.60 -Publisher EDOM04\SQLstandard
2007-09-12 12:05:46.60 -PublisherDB TMS4X_PublicationDB
2007-09-12 12:05:46.60 -Publication TMS4X_PublicationTest
2007-09-12 12:05:46.60 -ReplicationType 2
2007-09-12 12:05:46.60 -Distributor EDOM04\SQLstandard
2007-09-12 12:05:46.60 -DistributorSecurityMode 1
2007-09-12 12:05:46.60 -PublisherSecurityMode 1
2007-09-12 12:05:46.60 --
2007-09-12 12:05:46.63 Connecting to Distributor 'EDOM04\SQLstandard'
2007-09-12 12:05:46.96 The replication agent had encountered an exception.
2007-09-12 12:05:46.96 Source: Replication
2007-09-12 12:05:46.96 Exception Type: Microsoft.SqlServer.Replication.ReplicationAgentSqlException
2007-09-12 12:05:46.96 Exception Message: You do not have sufficient permission to run this command.
Contact your system administrator.
2007-09-12 12:05:46.96 Message Code: 14260
2007-09-12 12:05:46.96

Configurations:
-All Users have rights for read and write on the snapshotfolder including the Agent
-All users are defined in the same windows domain
-the snapshotagent account has sysadmin rights on the server and is assigned to the predefined MS SQL User Role

This scenario is workin completely fine when i exceute everything as a "sysadmin"!
But when executing it all as "db_owner" of the database, its not workin!!!

Does anybody has any resolutions for this problem?
I appreciate any support.
MariJo

Hi,

Just double check, when you say the account is 'db_owner', is it an db_owner of both publication database and distribution database? Replication requires both. For complete replication agent security requirement, you can refer to http://technet.microsoft.com/en-us/library/ms151868.aspx

If it still does not work, please let me know.

Peng

|||Hi,

thx for your reply.
I read already a lot about it in the msdn, also that link that you mentioned. I found out about the proxies and credentials used by sql server.

Following:
When creating the publication by a User (db_owner), it only works when the User has db_owner rights for both, the publication and distribution database. But i dont want to grant this user account the db_owner rights for the system database 'distributionDb'. Thats i would like to solve it by impersonating and that process account for the agent.

I also created the windows account "repl_snapshot" just for the Snapshot Agent.
When creating the Snapshot agent for the publication, i also use the "impersonating ... process account", therefore the "repl_snapshot" should excecute the snapshot creation, cause the agent is defined like this. When creating this snapshot agent: the proxy and credential are automatically created.
Next point is that i need to assign the account User1 (db_owner) to that proxy, right?!
But then i get the message:
it starts the agent ... success
executing ... no rights!

and then! Big problem ... nothing works anymore on that database. and then i need to use the sp_removedbreplication and create a new db.

So i dont understand completely how to configure this proxy for this agent job. Cause i found out, that i need to assign the proxy to each agent job step, but i cant do that! cause when i open the agent job to assign the proxy to the steps: no steps are shown. But when starting the agent job over the context menu, i shows the three steps, sth like: start egent, execute, end.

What do you think? Am i on the right way?

I think, I will have the same problems when creating/Synchronizing a subscription form an sql express server for the merge Replication at the end, cause there is also the 'repl_merge' merge agent as the process account.

MariJo
|||

Hi,

When you create the publication and configure snapshot agent, there are two sections in the "snapshot agent security" dialog: a windows account that snapshot agent process that runs under (snapshot agent use this account to connect to distributor), and if you would like to connect to publisher by impersonating process account. You need to assign the db_owner role to the proxy account to the distribution db and publication db (if you choose to connect to publisher by impersonating process account). From your reply, it is still not clear to me if you assign the db_owner role to the proxy account at both distribution/publication DB.

To use another proxy account or simply sqlagent service account for snapshot agent, goto "publication properties" dialog and choose "agent securities" tab and you should be able to launch "snapshot agent security" dialog and modify it.

Peng

|||

hi,

Sorry if it wasnt clear. Indeed, i did assign the db_owner role to the proxy account at both distribution/publication DB.
Its just that i want to run that Snapshot agent in a context of a proxy and thats not working, so i found out sth on the Support page of Microsoft. Microsoft has a hotfix for exactly that problem, i think ... i didnt try it yet ... but for sure im going to do that on monday.

Hotfix: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005, http://support.microsoft.com/kb/938086

Will let you know for more when i've tested it ...

MariJo

|||Its working with this hotifx!!!!
So, wait for SP3 or get this hotfix update if you really need it, but the hotfix is not fully tested and official.

Regards,
MariJosql

Create Procedure Permission ONLY

I have a requirement in SQL 2005 in Development database

1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables .
Developer's should not create or alter tables .
3. Developers can create/alter Stored Procedure/User Defined functions
in dbo schema and can execute SP/UDF.
4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
tables in dbo schema

How to achieve this using GRANT SCHEMA statement

Thanks

M A Srinivas(masri999@.gmail.com) writes:

Quote:

Originally Posted by

I have a requirement in SQL 2005 in Development database
>
1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables .
Developer's should not create or alter tables .
3. Developers can create/alter Stored Procedure/User Defined functions
in dbo schema and can execute SP/UDF.
4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
tables in dbo schema
>
How to achieve this using GRANT SCHEMA statement

The users need ALTER, SELECT, UPDATE, INSERT and DELETE permissions on
the schema and CREATE PROCEDURE and CREATE FUNCTION permissions on
the database. This script demonstrates:

CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//'
CREATE USER testdev

GRANT ALTER ON SCHEMA::dbo TO testdev
GRANT CREATE PROCEDURE TO testdev
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev

CREATE TABLE mysig (a int NOT NULL)

EXECUTE AS USER = 'testdev'
go
CREATE PROCEDURE slaskis AS PRINT 12
go
CREATE TABLE hoppsan(a int NOT NULL) -- FAILS!
go
INSERT mysig (a) VALUES(123)
go
REVERT
go
DROP PROCEDURE slaskis
DROP TABLE mysig
DROP USER testdev
DROP LOGIN testdev

--
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|||Thank you Erland

Thanks
Srinivas
Erland Sommarskog wrote:

Quote:

Originally Posted by

(masri999@.gmail.com) writes:

Quote:

Originally Posted by

I have a requirement in SQL 2005 in Development database

1. Schema dbo owns all objects (tables,views,SPs,UDFs etc) .
2. Only DBA's ( who are database owners ) can create, alter tables .
Developer's should not create or alter tables .
3. Developers can create/alter Stored Procedure/User Defined functions
in dbo schema and can execute SP/UDF.
4. Developers should have SELECT,INSERT,DELETE,UPDATE on tables (
tables in dbo schema

How to achieve this using GRANT SCHEMA statement

>
The users need ALTER, SELECT, UPDATE, INSERT and DELETE permissions on
the schema and CREATE PROCEDURE and CREATE FUNCTION permissions on
the database. This script demonstrates:
>
CREATE LOGIN testdev WITH PASSWORD = 'sldkjlkjlkj 987kj//'
CREATE USER testdev
>
GRANT ALTER ON SCHEMA::dbo TO testdev
GRANT CREATE PROCEDURE TO testdev
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO testdev
>
CREATE TABLE mysig (a int NOT NULL)
>
EXECUTE AS USER = 'testdev'
go
CREATE PROCEDURE slaskis AS PRINT 12
go
CREATE TABLE hoppsan(a int NOT NULL) -- FAILS!
go
INSERT mysig (a) VALUES(123)
go
REVERT
go
DROP PROCEDURE slaskis
DROP TABLE mysig
DROP USER testdev
DROP LOGIN testdev
>
>
--
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 permission

Is there such granularity in SQL 2K security that might allow a user to
create a temp table (as in a stored proc) but restrict them from creating a
permanent user table?
Message posted via http://www.sqlmonster.com
All users can create temp tables by default.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4b1a04ce369e4b069715c04b3466fdf2@.SQLMonster.c om...
> Is there such granularity in SQL 2K security that might allow a user to
> create a temp table (as in a stored proc) but restrict them from creating
> a
> permanent user table?
> --
> Message posted via http://www.sqlmonster.com

Create permission

Is there such granularity in SQL 2K security that might allow a user to
create a temp table (as in a stored proc) but restrict them from creating a
permanent user table?
Message posted via http://www.droptable.comAll users can create temp tables by default.
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Robert Richards via droptable.com" <forum@.droptable.com> wrote in message
news:4b1a04ce369e4b069715c04b3466fdf2@.SQ
droptable.com...
> Is there such granularity in SQL 2K security that might allow a user to
> create a temp table (as in a stored proc) but restrict them from creating
> a
> permanent user table?
> --
> Message posted via http://www.droptable.com

Create permission

Is there such granularity in SQL 2K security that might allow a user to
create a temp table (as in a stored proc) but restrict them from creating a
permanent user table?
--
Message posted via http://www.sqlmonster.comAll users can create temp tables by default.
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Robert Richards via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:4b1a04ce369e4b069715c04b3466fdf2@.SQLMonster.com...
> Is there such granularity in SQL 2K security that might allow a user to
> create a temp table (as in a stored proc) but restrict them from creating
> a
> permanent user table?
> --
> Message posted via http://www.sqlmonster.com

CREATE DATABASE permission denied in database master. error

got rid of my error about user login rights, it was all working yesterday. but for some reason i now get this error

 CREATE DATABASE permission deniedin database'master'.An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.Description: An unhandled exception occurred during the execution of the current web request. Please review the stack tracefor more information about the error and where it originatedin the code.Exception Details: System.Data.SqlClient.SqlException: CREATE DATABASE permission deniedin database'master'.An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.Source Error:An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identifiedusing the exception stack trace below.Stack Trace:[SqlException (0x80131904): CREATE DATABASE permission deniedin database'master'.An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.] System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +734995 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1838 System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33 System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +628 System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170 System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359 System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28 System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424 System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66 System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496 System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82 System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105 System.Data.SqlClient.SqlConnection.Open() +111 System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +121 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +137 System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +83 System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +1770 System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +17 System.Web.UI.WebControls.DataBoundControl.PerformSelect() +149 System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +70 System.Web.UI.WebControls.GridView.DataBind() +4 System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82 System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +69 System.Web.UI.Control.EnsureChildControls() +87 System.Web.UI.Control.PreRenderRecursiveInternal() +41 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Control.PreRenderRecursiveInternal() +161 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1360

i have this in my web.config file

<connectionStrings>
<add name="ConnectionStringTest" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf;Integrated Security=SSPI;Connect Timeout=30;User Instance=False"
providerName="System.Data.SqlClient" />
</connectionStrings>
<appSettings />

<system.web>
<!--
Set compilation debug="true" to insert debugging symbols into the compiled page.
Because this affects performance, set this value to true only during development.
-->
<compilation debug="true" />
<identity impersonate="true"/>

and my asp connection string is

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionStringTest %>"
SelectCommand="SELECT [entryID], [compID], [emailAddy], [answer] FROM [entry]"></asp:SqlDataSource>

if i set user instance to true i get a user permission error.

it says on sql server management that i have dbo rights on my database, but it wont let me put datareader or write on this login. any ideas? its driving me insane

Looks like SQL server could not connect you file to system

An attempt to attach an auto-named databasefor file C:\Inetpub\wwwroot\sqlSite\App_Data\siteDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or itis located on UNC share.

so it automatically switched you to master database which is default for new users and probably you have no rights to create table in it.

Is your database file local to your server?

Does you SQL server user accont has rights to access this database file?

Check it.

Thanks

|||

Is your database file local to your server? - yup

Does you SQL server user accont has rights to access this database file? - i only have 1 user account - funkymp which i log onto my computer which, it says on sql management tool that im the dbo for the database

|||

if i go into databases - databasename - security - users i have dbo (which is the funkymp account), guest, info schema, rob\aspnet and sys.

if i go into securiy - logins, i have funkymp there again, default database is master - should i change this to the database im trying to access? this is driving me up the wall had the same error for 2 das now, are there any sql commands that i can run as a new query to check if i have the rights to access that table with the user funkymp?

|||

Hi,

It seems that you might not be connecting to the correct database.

Please try to add Initial Catalog=<DatabaseName> in your connection string.

HTH.

CREATE DATABASE permission denied in database ''master''. (Microsoft SQL Server, Error: 262)

How exactly do I correct this problem on Vista?

TITLE: Microsoft SQL Server Management Studio Express

Create failed for Database 'kkl'. (Microsoft.SqlServer.Express.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.3042.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476

ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.Express.ConnectionInfo)

CREATE DATABASE permission denied in database 'master'. (Microsoft SQL Server, Error: 262)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.1399&EvtSrc=MSSQLServer&EvtID=262&LinkId=20476

I have ensured that I am logged into the system as Admin but cannot find where on the Server, I assign my login to the dbcreator group.

I have gone into Security >> Server Roles >> dbcreator. of which I am not a member. On a different system, where all works, however, I am not a member of this group either.

Any suggestions on how to kill this one?

Thanks in Advance.

Klaus

and this fixed my problem:

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

CREATE DATABASE permission denied in database 'master'

Hi,

I'm using Visual Studio 2005 Pro and SQLExpress that comes with it.

I have my program running fine in XP Pro OS using a window user "Glen" (Computer administrator) with Administrator rights. This means that I installed VS 2005 using this window user "Glen"

I created another windows user "TestUser" (Limited account) in the same physical PC.

I tried to run the program and on the part that I need to access SQL table, I got the error [CREATE DATABASE permission denied in database 'master']

At the same time while using "TestUser" and running sqlcmd (to check if I can connect to SQL), I also got error HResult 0x2, Level 16, State 1.

I read alot on MSDN discussions and related links but it seems that I can't get the solution that I need.

SO HERE ARE MY QUESTIONS :

1. Am I allowed to run my program using user "TestUser" since SQL is installed using "Glen" windows user?

2. Do I need to add access rights to "TestUser" to allow the user to have CREATE rights? (Note : for security reason, I can add other access rights except Administrator)

Thanks in advance for all you help.

It seems there are few things going on here. Let's take a moment and break each one down.

First, the account used to install SQL Server is normally a System Administrator. What's more important are the accounts used to start the services for SQL Server. You have a few choices there, but most often it's best to use a regular account, rather than LocalSystem or NetworkingSystem. You can find out more about that in Books Online searching for "Services" . When you install SQL Server, by default the local Windows Administrator's group is placed in the SQL Server sysadmin Role, which allows all rights for everything. Other users don't have access at all (yet).

Security inside SQL Server is independent of the installation or the startup accounts. Since the "Glen" account is a local administrator, he can do anything he wants in SQL Server. If you created a "TestUser" server login, they can connect, but they can't do anything else. You'll need to assign them a database, create a user in the database tied to the "TestUser" login, and grant rights there.

There are server-level rights, and database-level rights. The CREATE DATABASE statement is a server-level right, and most users don't need that.

Books Online has a great set of topics on SQL Server Security that will help you sort all this out. You can also see my articles on Security starting here:

http://www.informit.com/guides/content.asp?g=sqlserver&seqNum=35&rl=1

Buck Woody

|||

Thanks for a quick reply Buck.

I have another question related to your answer. You mentioned about "LocalSystem" or "NetworkingSystem".

Are you pertaining to the Log On tab section "Log on as:" found in the SQL Server (SQLEXPRESS) Properties in the SQL Server Configuration Manager? Are you advising me to select "This account:" and create a user from there?

I will read more on the Online Books at the same time. This is to know the database that I need to assign to "TestUser". I am guessing here if you are talking about the application database or the database originally in the SQLEXPRESS like the master, model, etc.

Thanks again.

|||

That's right. You can also set that in the Services applet of the Control Panel.

The application database is the only one that needs a user account, in addition to a server login. If you check that site on InformIT, you'll see a reference to those.

Buck