February 19, 2008 at 6:58 am
Hello,
Can i give any privilege to one user so that he can view databases inside SQL Server Management Studio.
The command that someone here execute was:
use master
Revoke View any database from public
go
Now i which to give to one user the privilege to he can see one specific database, but not all if its possible.
Other question is, how can i grant privileges to users that are dbo, but are not database owners (sp_changedbowner1)?
use db
go
Exec Sp_changedbowner 'username'
Thanks and regards,
JMSM;)
February 19, 2008 at 8:59 pm
I might have misread your query. You can grant access to a user to a specific database and he will not be able to access other databases available in that instance.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
February 20, 2008 at 7:44 pm
Once you revoke the View Any Database, if you then execute sp_changedbowner for database X for a given login that login should see that database, as well as master and (I think tempdb. Note though that the login must be the owner, not just a member of the db_owners database role.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 21, 2008 at 11:08 am
You don't need to make the login owner of the database in order to view the database. Not unless you're wanting that user to be able to alter database properties.
The problem is, we're not quite sure what you're trying to achieve with this user being able to see the database. What exactly will this user be doing on the database? Will he be a DBO in this database or is he just a DBO in another database and you want him to see this new database also?
Please define the word "see" (i.e., this user's anticipated tasks) and we'll give you more specifics on what permissions you need to grant in order for this user to do what he needs to do.
February 21, 2008 at 1:52 pm
Brandie, I took this as the individual has SSMS and should be able to "see" only a specific database(s) in the Databases node of the Object Explorer and not "see" other databases that are hosted on the SQL instance.
To do this, you Deny VIEW ANY DATABASE to a login, and if you then alias that login as the owner in a database when they connect to SSMS they will see only master, tempdb, and any database they are the owner of in the Databases node of the Object Explorer.
They still will not be able to see a database in the Databases node even if they are a member of the db_owners role.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 22, 2008 at 4:23 am
And that might be true, but the OP might have meant something different too. So, I'd like to hear from him regarding what he's trying to accomplish to make sure we got him the right answer to the question.
February 22, 2008 at 9:00 am
Definitely agree Brandie, just wanted to explain for any lurkers out there so that they just don't go around giving out dbo under the belief that they need to in order for users to connect to the database.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
February 22, 2008 at 10:00 am
Thanks a lot everybody.
Regards,
JMSM
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply