In SQLServer, why doesn't granting object permissions add the user to db properly?

Question

This is my setup:

• SQL server 2005
• UserA already setup as user on the server but not on databaseA.
• GRANT Execute ON [GetOrders] TO [UserA] AS [dbo]

As you can see, i have missed out a step. I haven't added UserA to the database yet. This doesn't fail and lets me grant execute on the object.

I believe in 2000 it would have thrown an error and not allowed me to do this.

Now, I have all these objects with the correct permissions set but the users cannot see the database. To resolve this i have to remove the users from the database, re-add them properly and then give permissions.

Why is this allowed and is there a way to either prevent it or have it create the db login automatically whenever a new user is given object permissions.

thanks.

Answer 1

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

Answer 2

This is the part throwing you off.

TO [UserA] AS [dbo]

Ditch the "as dbo" part. It's granting the right to the dbo user. Without the "AS [dbo]" part it will throw an error.