August 15, 2012 at 12:04 pm
Hello:
1.-UserID abc created
2.-DBRole abcRole created
3.-Several tables with schema abcschema and xyzschema exist
4.-Add userID abc to role abcRole
5.-on schema abcschema I add role abcRole
6.-on schema abcschema I grant select,insert,update,delete to role abcRole
Questions:
1.- login as abc
2.-I can not see any tables in the database, there are 45 tables with sbcschema, why?
3.-what I am missing?
4.-UserID abc does not have any grants.... since it is part of role abcRole
Please help.
Puzzle
August 15, 2012 at 1:28 pm
Pablo Campanini-336244 (8/15/2012)
Hello:1.-UserID abc created
2.-DBRole abcRole created
3.-Several tables with schema abcschema and xyzschema exist
4.-Add userID abc to role abcRole
5.-on schema abcschema I add role abcRole
6.-on schema abcschema I grant select,insert,update,delete to role abcRole
Questions:
1.- login as abc
2.-I can not see any tables in the database, there are 45 tables with sbcschema, why?
3.-what I am missing?
4.-UserID abc does not have any grants.... since it is part of role abcRole
Please help.
Puzzle
I cannot recreate your issue. When I run this and log in as testLogin I can see the table:
CREATE DATABASE someDatabase
GO
USE someDatabase
GO
CREATE USER abc FROM LOGIN testLogin
GO
CREATE SCHEMA abcschema
GO
CREATE TABLE abcschema.t1 (id INT)
GO
CREATE ROLE abcRole
GO
EXEC sys.sp_addrolemember
@rolename = 'abcRole',
@membername = 'abc'
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA::abcschema TO abcRole
GO
Retrace your steps.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 15, 2012 at 2:18 pm
Hi
The command:
CREATE USER abc FROM LOGIN testLOGIN
creates a "database" login, not a SQL Instance login.
Please create also the sql ID at an Instance level.
Then connect to the SQL Instance with the new user abc.
try to display the tables on the new database.
Let me know what you get.
Thanks for your help.
Pablo
August 15, 2012 at 2:22 pm
Pablo Campanini-336244 (8/15/2012)
HiThe command:
CREATE USER abc FROM LOGIN testLOGIN
creates a "database" login, not a SQL Instance login.
Please create also the sql ID at an Instance level.
Then connect to the SQL Instance with the new user abc.
try to display the tables on the new database.
Let me know what you get.
Thanks for your help.
Pablo
I have an idea, how about you post the T-SQL statements you used? Then we can see what happens when we follow the exact same steps you did.
August 15, 2012 at 2:26 pm
Pablo Campanini-336244 (8/15/2012)
HiThe command:
CREATE USER abc FROM LOGIN testLOGIN
creates a "database" login, not a SQL Instance login.
Please create also the sql ID at an Instance level.
Then connect to the SQL Instance with the new user abc.
try to display the tables on the new database.
Let me know what you get.
Thanks for your help.
Pablo
testLogin was a pre-existing login on my instance. You left the step of creating a new server login out of your post, so I left it out of my repro code. I still never caught what you meant in your step 5, but I let it go.
I'll have to second what Lynn said. Please share your code.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 15, 2012 at 2:37 pm
Hello, thanks for your help...... here is what I do:
Let me know if it works.....
CREATE DATABASE someDatabase
GO
USE someDatabase
GO
CREATE USER abc FROM LOGIN imstestuser<< this is also a SQL ID that I already have
GO
USE [master]
GO
CREATE LOGIN [abc] WITH PASSWORD=N'abc', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE someDatabase
GO
CREATE SCHEMA abcschema
GO
CREATE TABLE abcschema.t1 (id INT)
GO
CREATE ROLE abcRole
GO
EXEC sys.sp_addrolemember
@rolename = 'abcRole',
@membername = 'abc'
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA::abcschema TO abcRole
GO
August 15, 2012 at 2:45 pm
Pablo Campanini-336244 (8/15/2012)
Hello, thanks for your help...... here is what I do:Let me know if it works.....
CREATE DATABASE someDatabase
GO
USE someDatabase
GO
CREATE USER abc FROM LOGIN imstestuser<< this is also a SQL ID that I already have
GO
USE [master]
GO
CREATE LOGIN [abc] WITH PASSWORD=N'abc', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE someDatabase
GO
CREATE SCHEMA abcschema
GO
CREATE TABLE abcschema.t1 (id INT)
GO
CREATE ROLE abcRole
GO
EXEC sys.sp_addrolemember
@rolename = 'abcRole',
@membername = 'abc'
GO
GRANT SELECT,INSERT,UPDATE,DELETE ON SCHEMA::abcschema TO abcRole
GO
CREATE USER abc FROM LOGIN imstestuser<< this is also a SQL ID that I already have
GO
USE [master]
GO
CREATE LOGIN [abc] WITH PASSWORD=N'abc', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
User abc is related to the LOGIN imstestuser. You then subsequently create a new login abc. This login is not the same as the user abc.
Flip the CREATE USER and CREATE LOGIN and replace imstestuser in the CREATE USER with abc.
August 15, 2012 at 2:58 pm
Lynn Pettis (8/15/2012)
User abc is related to the LOGIN imstestuser. You then subsequently create a new login abc. This login is not the same as the user abc.Flip the CREATE USER and CREATE LOGIN and replace imstestuser in the CREATE USER with abc.
+1, there is your issue.
One other item of note for clarity sake and future reference, earlier you said:
The command:
CREATE USER abc FROM LOGIN testLOGIN
creates a "database" login, not a SQL Instance login.
There is no such thing as a database login. There are server logins and database users. It's important to use the understand each entity separately, and how they interact. If you're mixing the idea of a login and a user that could be the source of some of the confusion.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 16, 2012 at 7:03 am
Hi everybody:
Thank you for your time and responses, but let me explain what my specific problem is, maybe this way I can show what my problems is:
Now in SQL Server 2008, we have schemas and roles which we can use to grant access to databases. We have a database with over 100 tables with 3 different schemas, we also created database roles with the same name for each schema e.g. schema abc, roles abc.
OK, now here are the steps that I did, and still could not see the tables:
1.-Database name is IMS_DIAGRAM
2.-Create the LOGIN ID in the SQL Instance
USE [master]
GO
CREATE LOGIN [U1] WITH PASSWORD=N'U1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
3.-Then I add this new login to the database:
USE [IMS_DIAGRAM]
GO
CREATE USER [U1] FOR LOGIN [U1]
GO
So far I have the login in the SQL Instance and in the database IMS_DIAGRAM. Now I will create a new role in the IMS_DIAGRAM database:
4.-USE [IMS_DIAGRAM]
GO
CREATE ROLE [abc]
GO
I have now my new User ID, and my new database role. Next is add user ID U1 to the new database role abc
5.-USE [IMS_DIAGRAM]
GO
EXEC sp_addrolemember N'abc', N'U1'
GO
Great, now the only thing is left to do is to grant to the schema select,insert,update,delete, here is the command. please take in consideration that this schema alrewady exist on the database IMS_DIAGRAM with over 30 tables, schema name is "Common".
6.- use [IMS_DIAGRAM]
GO
GRANT DELETE ON SCHEMA::[Common] TO [U1]
GO
use [IMS_DIAGRAM]
GO
GRANT INSERT ON SCHEMA::[Common] TO [U1]
GO
use [IMS_DIAGRAM]
GO
GRANT SELECT ON SCHEMA::[Common] TO [U1]
GO
use [IMS_DIAGRAM]
GO
GRANT UPDATE ON SCHEMA::[Common] TO [U1]
GO
So I "connect" to the SQL Instance with U1 ID, but when I go to the database to display the tables, I got nothing. I sould be able to see at least the tables with schema 'Common'. That is my problem.
🙁
August 16, 2012 at 7:09 am
CORRECTION, sorry, the following is the last command, the grant is to the ROLE not to the User ID.
Still did not work..... 🙁
use [IMS_DIAGRAM]
GO
GRANT DELETE ON SCHEMA::[Common] TO [abc]
GO
use [IMS_DIAGRAM]
GO
GRANT INSERT ON SCHEMA::[Common] TO [abc]
GO
use [IMS_DIAGRAM]
GO
GRANT SELECT ON SCHEMA::[Common] TO [abc]
GO
use [IMS_DIAGRAM]
GO
GRANT UPDATE ON SCHEMA::[Common] TO [abc]
GO
August 16, 2012 at 7:18 am
Really having a problem here. I followed your steps and I don't have a problem connecting with the user U1 and I see the table in the Common schema that I created from my admin account. I can insert data, select data, change data and delete data.
August 16, 2012 at 8:57 am
WOWWWWWW....
Listen to this....
Yes I can not see the tables thru SSMS when I am logged in as U1,
BUT if I do a select on the tables with schema 'Common'... IT WORKS !!!!!!!
I wonder why in SSMS I can not look at the tables, maybe because i 'just' have read, insert,update and delete access, and not able to see the columns of the tables and other information.
Weird......;-)
Thanks for your help !!!!!!
August 16, 2012 at 9:08 am
Pablo Campanini-336244 (8/16/2012)
WOWWWWWW....Listen to this....
Yes I can not see the tables thru SSMS when I am logged in as U1,
BUT if I do a select on the tables with schema 'Common'... IT WORKS !!!!!!!
I wonder why in SSMS I can not look at the tables, maybe because i 'just' have read, insert,update and delete access, and not able to see the columns of the tables and other information.
Weird......;-)
Thanks for your help !!!!!!
That really is weird, because when I connected to SQL Server using the U1 user in SSMS, I could see the table I had created.
August 16, 2012 at 9:14 am
Happy you got it sorted. I was curious so I did some looking to see if others had this issue and it looks like it may be a known bug in SMO that affects SSMS 2008:
Tables Node Does Not Show All Schemas In SSMSE 2008
I am using SSMS 2008 R2 so maybe I am not affected because I too cannot recreate your issue.
Please confirm you are using SSMS 2008 and not 2008 R2. When logged into an SSMS 2008 query window as U1 what does this tell you:
USE IMS_DIAGRAM
GO
SELECT name
FROM sys.tables
WHERE SCHEMA_NAME(schema_id) = 'Common'
ORDER BY name;
GO
If you can see the tables in your results then it confirms it is not a database engine permissions issue and simply a display issue with SSMS likely due to a bug.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
August 16, 2012 at 9:30 am
YES......
You are absolutely right, I am using SQL Server 2008, not SQL Server 2008 R2, and the bug is killing me!!!!!!.
I will ask my boss to upgrade to R2.
Great reasearch!!!!
Thanks you guys......:-)
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply