| Author |
Topic |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2009-08-12 : 13:52:41
|
| I have to create some new stored procedures and views to accomplish some reporting in a database created by a vendor. I don't like the idea of mixing all of my new objects with their objects. Is there a good naming convention or other way to keep them separate?Duane |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-08-12 : 14:26:20
|
| You could create a new database and put your stored procedures in there and just refernce the other database or you could create a new schema and use that to separate your object. There might be some other ways, but those are two off the top of my head. |
 |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-12 : 14:28:02
|
| schema is probably your best option |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-08-12 : 14:34:49
|
| Thank you for the idea. I was thinking about that anyway. I just wasn't sure. If I do that, how do I refer to the new schema, or for that matter the dbo schema that everything else is written in? Also, is there any pitfalls I need to know about like assigning privileges or anything else to make the schema work. I don't want to introduce any new problems.Duane |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-12 : 14:35:59
|
| prefix objects with schema name.select * from dbo.tableNameselect * from mySchema.tableNameEXEC myShema.myStoredProc |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-08-12 : 14:37:52
|
| Thank you. Are there any privileges, etc., that I need to set in order for them to work?Duane |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-12 : 14:39:38
|
of course. same as always.  |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-08-12 : 14:49:58
|
| I am not sure how to do that. Is there a quick explanation? I want to do it but am not familiar with what has to happen.Duane |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-12 : 15:12:24
|
| by the way, if your stuff is all new and does not interact with existing objects/data, I'd make a new database.if it needs to be interspersed with existing, then this is basically what schemas are for let us know if the above links straighten it out for you. else post back. |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-08-12 : 15:20:53
|
| Just to be quick, and I do have the permissions, I would like to create a schema with as close to dbo properties as I can so I have no problems. I just want it so I can distinguish my objects, (I will probably make the schema name my company's initials) from those of the vendor. The msdn documents above look a bit chewy when I don't have any examples. I don't know who or what permissions to give to whom or what, and if roles have anything to do with it, and anything else. These stored procedures I will make with this schema will be run by Report Server and will be run by different types of users. I could go on and on. I do appreciate the help, but I just want to know that anyone who runs a report from anywhere won't have any problems with the SQL Server database end of it just because I created a new schema. I just want it to run like .dbo. It is interspersed with the vendor databse. It is not new. I am just reporting from this database for now.Duane |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-08-12 : 15:37:55
|
| ok, this is no different than you're used to. you grant permissions the same as you always did before when just using dbo.how will you be setting up users? will there be many users or just one or two for the application?it is typical to add users to a role. this can be an existing database role (like db_datareader, db_datawriter, db_owner etc.) or you can create a custom role and add users to that.go ahead and test it in a non-critical database outside of your project. Create a schema, then a table and an SP in that schema. Create a user. Make that user db_datareader in that database.log in as that user and select * from the table.works just like before when u used dbo |
|
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2009-08-12 : 16:13:32
|
| I know very little about granting permissions even on dbo. I also know very little to nothing about setting up users and setting up roles. I have just come into this office and have been asked to create reports directly from reporting services that are now coming out of an application that is connected to this database. Everything works as is. I have created a few stored procedures in dbo and gotten reports from them with Reporting Services. I want to know if I can just "Create schema MySchema" and create procedures on it just like I can now on dbo and expect them to run. I could put a few special initials in SPs in dbo to identify my own and they would obviously work the same as any other in dbo. I want it to be that easy, just as if it were a naming convention. I am on a time crunch and don't want to create new problems. Can I "script" it after dbo so everything is identical but the name? If there are a few settings, I can handle that. But I don't know how they have dbo set up. It just works with everything they have. I want to learn more, but there are too many things to grasp now - SQL Server permissions, logins, users, roles; Reporting Services permissions, etc. I just don't want to open a new can of worms that I don't understand.Duane |
|
|
|
|
How should I store objects I create on database?