December 5, 2011 at 8:44 am
Hi,
I need to create a situation where all my database tables are prefixed with dba rather than dbo, for example dbo.customers would be dba.customers.
I'm thinking I can create a role called dba, assign a user to it, and then somehow make sure when the data is imported into the empty database (using SSIS), that all tables will end up in the DBA schema.
The only problem is I'm not sure how to. Can anyone advise please?
Thanks
December 5, 2011 at 8:58 am
Paula here's an example i built for another post, I think it describes everything you are looking for.
the key is to apply a default schema to the users; then any objects they create or reference are assumed to be in that specific schema and not dbo.
take a look at this, and let us know if you need some clarifications.
/*
USE master;
DROP DATABASE SCHEMATEST;
*/
CREATE DATABASE SCHEMATEST;
GO
USE SCHEMATEST;
GO
--create sample schemas
CREATE SCHEMA ORANGE;
GO
CREATE SCHEMA GREEN;
GO
--Create the Roles used to control permissions
CREATE ROLE ORANGEROLE;
CREATE ROLE GREENROLE;
--grant full permissions to the roles for the correct schemas to existing objects
--not this only gives the user access to objects already created
GRANT CONTROL ON SCHEMA :: ORANGE TO ORANGEROLE;
GRANT CONTROL ON SCHEMA :: GREEN TO GREENROLE;
--per BOL, you also need CREATE TABLE permissions,
--otherwise you can just fiddle with existing previously created objects
GRANT CREATE TABLE TO ORANGEROLE;
GRANT CREATE TABLE TO GREENROLE;
--more granular? other options below
--GRANT ALTER ON SCHEMA :: ORANGE TO ORANGEROLE;
--GRANT EXECUTE ON SCHEMA :: ORANGE TO ORANGEROLE;
--GRANT SELECT ON SCHEMA :: ORANGE TO ORANGEROLE;
--GRANT INSERT ON SCHEMA :: ORANGE TO ORANGEROLE;
--GRANT UPDATE ON SCHEMA :: ORANGE TO ORANGEROLE;
--GRANT DELETE ON SCHEMA :: ORANGE TO ORANGEROLE;
--create some test users
CREATE USER ORANGEUSER WITHOUT LOGIN;
CREATE USER GREENUSER WITHOUT LOGIN;
--assign them to use this schema s a default.
--this is important, as if they create a table, you want it to be unde rthe new schema, and not try to be placed in dbo.
ALTER USER ORANGEUSER WITH DEFAULT_SCHEMA = ORANGE;
ALTER USER GREENUSER WITH DEFAULT_SCHEMA = GREEN;
--let them build objects only in their schema
EXEC sp_addrolemember 'ORANGEROLE', 'ORANGEUSER'
EXEC sp_addrolemember 'GREENROLE', 'GREENUSER'
--still with my superman cape on, I'll create three tables, one in each schema dbo/Orange/Green
CREATE TABLE [dbo].[tblBananas] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
CREATE TABLE [Orange].[tblBananas] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
CREATE TABLE [Green].[tblBananas] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
EXECUTE AS USER = 'ORANGEUSER'; --change to Clark Kent
--will this create dbo.tblApples or Orange.tblApples
CREATE TABLE [Orange].[tblApples] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
ALTER TABLE Orange.tblBananas ADD Descrip varchar(30)
select * from sys.tables --other tables exist, but this view shows only MY schema...not green and dbo!
REVERT;
EXECUTE AS USER = 'GREENUSER'; --change to Lois Lane
--will this create dbo.[tblCherries] or Orange.[tblCherries]
CREATE TABLE [Green].[tblCherries] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
--because of the DEFAULT_SCHEMA command above, any objects this user creates go under the "GREEN" schema.
--note the schem was not specified this time.
CREATE TABLE [tblApples] (
[myguid] uniqueidentifier NULL,
[myguid2] uniqueidentifier NULL)
select * from sys.tables --other tables exist, but this view shows only MY schema...not orange and dbo!
REVERT;
Lowell
December 5, 2011 at 9:39 am
Thanks for this, it's great. I need to make sure the role has enough permissions to create, run and save SSIS packages. Is this enough?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply