May 5, 2010 at 8:42 am
Hi Guys,
I have a problem with SQL SERVER 2008 management studio. when I connect to sql 2005 database using SQL 2008 Management studio with a user having limited privileges. I can connect but i cannot see any tables, SPs or views under that database. Everyone is saying give 'view definition' permission to that user. But i donot want to give that permission.
But when I connect from 2005 Management studio using user with similar privileges, i can see all tables, sps, views.
Is there any problem with SQL 2008 SSMS ? or SQL 2008 SSMS doesnot support SQL2005 database anymore or any security changes on SQL 2008 SSMS ? or Is this a bug on SQL 2008 ?
Plz help me.
thanks
AKP
May 5, 2010 at 10:19 am
It does sound like a permissions issue. I've never had any problem working with 2005 from the 2008 SSMS. It is possible that SSMS tries to do things in 2008 that it didn't in 2005 or that it references different objects than 2005. Why can't they have view definitions? You want them to see the objects anyway.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 5, 2010 at 10:49 am
I can give view Definition permissions but my question is:
Are these permissions are not enough to view objects ?
USE [ABCDEV]
GO
CREATE ROLE [abc_usr_rl] AUTHORIZATION [dbo]
GO
GRANT DELETE ON SCHEMA::[abc] TO [abc_usr_rl]
GO
GRANT EXECUTE ON SCHEMA::[abc] TO [abc_usr_rl]
GO
GRANT INSERT ON SCHEMA::[abc] TO [abc_usr_rl]
GO
GRANT SELECT ON SCHEMA::[abc] TO [abc_usr_rl]
GO
GRANT UPDATE ON SCHEMA::[abc] TO [abc_usr_rl]
GO
USE [master]
GO
CREATE LOGIN [abc_usr] WITH PASSWORD=N'abc_usr', DEFAULT_DATABASE=[ABCDEV], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [ABCDEV]
GO
CREATE USER [abc_usr] FOR LOGIN [abc_usr]
GO
ALTER USER [abc_usr] WITH DEFAULT_SCHEMA=[abc]
GO
EXEC sp_addrolemember N'abc_usr_rl', N'abc_usr'
GO
The user abc_usr can see all objects in SQL 2005 SSMS without view definition but why cannot in SQL 2008 SSMS ? Even I cannot see object on SSMS 2008 SSMS with SQL 2008 Database with this user.
Is there any security changes on SQL 2008 SSMS ?
THanks
AKP
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply