Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
guillermojcontreras
Starting Member
1 Post |
 Posted - 2010-12-09 : 19:42:19
|
| Hi,Im trying to set permissions for a user in a DB but im having certain issues, any help will be appreciated, here is my scenario:What I want to achieve----------------------I want to create a user in a database which is able to:-Create Tables , Create Views , Functions , Create Store Procedures, Create Primary Keys on tables he created, Backup transaction logs, Backup Database data but not structure, see server and database reports and the user must be able to connect to the database obviusly.- be able to run functions and storeprocedures , see table structureI DONT want the same user to:- see/alter already created functions definitions-See/alter already created triggers definitions-See/alter already created storeprocedures definitionsSo far what ive done is the following:A) I created a SERVER LOGIN, which is mapped to the "example database" with the "dbo" schema as default , the login only has the public role assigned and the connect privilege.I also created a user named "user" in the database "example"without any role or schema assigned.B) on the "example" database , i created a role named db_exampleroleC) on the securables of that db_examplerole i added the "example" database and granted the permission i explained above, and denied almost everything else. For example i granted CREATE TABLE.D) i Assigned the "user" to the db_exampleroleE) refreshed and restarted the sql server.What is happening:I was able to block the functions, triggers, and storeprocedures, but the user isnt able to create a table.When i try to create a table/SP/Funcion i get the followin message:"The specified schema name "dbo" either does not exist or you do not have permission to use it"so what i did is that i added the dbo schema to the db_examplerole definition and granted alter and control permissions over the schema, but it is still not working.What am i missing, thanks u all for your help.-----------------------Thanks |
|
|
Sachin.Nand
2937 Posts |
Posted - 2010-12-13 : 08:07:16
|
quote:
I also created a user named "user" in the database "example"without any role or schema assigned.
What was the login name you used for the above user?PBUH |
 |
|
|
|
|
|
|
|
User permission on a Database --Help needed