Permissions Management

Hello,
I am trying to set up permissions to secure databases in our Developement. I
have set up "View any Database" to Deny at the Database level.
when i login as admin i see all the database while the rest see Master and
Temp DB. What do i have to do at the database level so i can make other
databases visible to appropriate personnel and also give them control such as
Create Tables, Update SP etc.
Thanks
MannyManny
If the user is an owner of db try the following
Create a new SQL login "login1"
? Create a user named ?login1? in master database
? Grant CREATE DATABASE to login1
? While impersonating login1, create a database called ?dbteste?
? Revoke CREATE DATABASE permission from login1
? Revoke VIEW ANY DATABASE permission from PUBLIC
? Register this server as login1
? From the ?login1? session, expand database tree. Now, you should see
master, tempdb, dbteste
? Grant VIEW ANY DATABASE to PUBLIC
? From the ?login1? session, you should see all the databases
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny|||So basically the user who is creating the database will be only one who can
view the database in the tree.
What i am trying to do is following:
I have users User1, User2, User3 and Databases as DB1, DB2 and DB3.
I would like to revoke Public from seeing all the databases.
User 1 should have access to DB1 (as DBO) and DB2 as read/write
User 2 should only have access to DB2 (DBO)
User 3 should have access to DB2 (DBO) and DB3 as read only.
Hope i am clear in explaining things
THanks
Manny
"Uri Dimant" wrote:
> Manny
> If the user is an owner of db try the following
> Create a new SQL login "login1"
> â?¢ Create a user named â'login1â' in master database
> â?¢ Grant CREATE DATABASE to login1
> â?¢ While impersonating login1, create a database called â'dbtesteâ'
> â?¢ Revoke CREATE DATABASE permission from login1
> â?¢ Revoke VIEW ANY DATABASE permission from PUBLIC
> â?¢ Register this server as login1
> â?¢ From the â'login1â' session, expand database tree. Now, you should see
> master, tempdb, dbteste
> â?¢ Grant VIEW ANY DATABASE to PUBLIC
> â?¢ From the â'login1â' session, you should see all the databases
>
>
> "Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
> news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> > Hello,
> >
> > I am trying to set up permissions to secure databases in our Developement.
> > I
> > have set up "View any Database" to Deny at the Database level.
> >
> > when i login as admin i see all the database while the rest see Master and
> > Temp DB. What do i have to do at the database level so i can make other
> > databases visible to appropriate personnel and also give them control such
> > as
> > Create Tables, Update SP etc.
> >
> > Thanks
> >
> > Manny
>
>|||Manny,
VIEW ANY DATABASE is a server level permission and cannot be granted nor
denied at the database level. To accomplish what you wish to do, you can
deny the permission to the public role (rather than to individual users) and
put users in the db_owner role of the database(s) they should have access.
This would then allow them to see only databases which they have ownership
of.
With regards to granting/revoking DDL permissions, I suggest you try out the
tool SQL CodeSecure. It allows you to set DDL permissions very easily, as
well as audit all changes modifications made to a database and even rollback
changes or recover deleted objects.
You can download the application from this location:
http://www.sql-labs.com/downloads/SQLCodeSecure.zip
HTH.
"Manny Chohan" <MannyChohan@.discussions.microsoft.com> wrote in message
news:5E4EAE5B-E79D-408C-98AC-946C8EC0566E@.microsoft.com...
> Hello,
> I am trying to set up permissions to secure databases in our Developement.
> I
> have set up "View any Database" to Deny at the Database level.
> when i login as admin i see all the database while the rest see Master and
> Temp DB. What do i have to do at the database level so i can make other
> databases visible to appropriate personnel and also give them control such
> as
> Create Tables, Update SP etc.
> Thanks
> Manny