adding ownername.tablename example?

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 10, 2008 at 11:43 am

  #125743

  I'm drawing a blank here today.

  I've got a nifty procedure that gives me table DDL statements; you know CREATE TABLE....

  that is constructed the way my company likes to read em..no problem.

  now i want to enhance it so it gets the ownername.tablename; that was easy,a s it's just the owner_name(uid) from sysobjects.

  my problem is making a test database; I want to add a new database, add a couple of users, and create two tables with the same name under different owner/schemas

  here's what i did, but when ii try to create the table bob.mytable, its failing...what am i doing wrong?

  [font="Courier New"]

  CREATE DATABASE WHATEVER

  IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'bob')

  BEGIN

  EXEC MASTER.dbo.sp_addlogin @loginame = N'bob', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

  --add this user to permit read and write

  END

  IF NOT EXISTS (SELECT * FROM MASTER.dbo.syslogins WHERE name = N'jeff')

  BEGIN

  EXEC MASTER.dbo.sp_addlogin @loginame = N'jeff', @passwd = 'NotARealPassword', @defdb = N'WHATEVER', @deflanguage = N'us_english'

  --add this user to permit read and write

  END

  USE [WHATEVER]

  CREATE USER [bob] FOR LOGIN [bob]

  CREATE USER [jeff] FOR LOGIN [jeff]

  EXEC sp_addrolemember N'db_ddladmin',   N'bob'

  EXEC sp_addrolemember N'db_datareader', N'bob'

  EXEC sp_addrolemember N'db_datawriter', N'bob'

  EXEC sp_addrolemember N'db_ddladmin',   N'jeff'

  EXEC sp_addrolemember N'db_datareader', N'jeff'

  EXEC sp_addrolemember N'db_datawriter', N'jeff'

  i tried this thinking i needed to fiddle with their schema instead of roles, but neither method seems to work for me....

  ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin]   TO [bob]

  ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [bob]

  ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [bob]

  so i assume the two users are ready to create stuff, or that sa/admin can create on their behalf. but it fails.

  [/color]CREATE TABLE bob.mytable(

  TID INT IDENTITY(1,1),

  mytext VARCHAR(30) )

  CREATE TABLE jeff.mytable(

  TID INT IDENTITY(1,1) PRIMARY KEY,

  mytext VARCHAR(30) ,

  anothercol VARCHAR(100))

  [/size][/font]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323518

  More actions

  December 10, 2008 at 11:53 am

  #910587

  ok my issue was thinking owner=login=schema

  this is what i had to do:

  [font="Courier New"]

  CREATE SCHEMA bob  AUTHORIZATION bob

  CREATE SCHEMA jeff AUTHORIZATION jeff

  CREATE TABLE bob.mytable(

  TID INT IDENTITY(1,1),

  mytext VARCHAR(30) )

  CREATE TABLE jeff.mytable(

  TID INT IDENTITY(1,1) PRIMARY KEY,

  mytext VARCHAR(30) ,

  anothercol VARCHAR(100))

  SELECT * FROM sysobjects WHERE name='mytable'[/font]

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!