Clever Geek Handbook

In SQLServer, why is granting permission not allowing the user to add the db correctly?

This is my setup:

  • SQL Server 2005
  • The user is already configured as a user on the server, but not on the database.
  • GRANT Execute ON [GetOrders] TO [UserA] AS [dbo]

As you can see, I missed a step. I haven't added UserA to the database yet. This won't work and allows the object to be executed.

I believe that in 2000 he would have been wrong and would not have let me do it.

I now have all of these objects with the correct permissions, but users cannot see the database. To solve this problem, I have to remove users from the database, add them correctly and then grant permissions.

Why is this allowed and is there a way to prevent it or create it automatically when a new user is granted object rights.

thank.

+1


source to share


2 answers


This is the part that throws you off.

TO [UserA] AS [dbo]



Detach the "like dbo" part. This gives the right to the dbo user. Without the "AS [dbo]" part, it throws an error.

+2


source


This likely allowed DBAs to have the flexibility to change users and permissions when the database is not connected to your production server. I don't believe you need to re-add users. Take a look at this guy's script: http://www.lazycoder.com/weblog/2007/06/04/re-associate-sql-users-with-logins/



+1


source






More articles:


All Articles