January 19, 2006 at 11:15 pm
Hi all,
It seems that every time you create a new database,you have to run these three store procedures. Can you help me for this.
Let's say the @username is yourServerName\ASPNET, in master database, you must have this user right?
If you run
exec sp_grantlogin yourServerName\ASPNET
in one of your databases.
And your create another new database on the same server, you don't need to execute the above statement right? The only thing you need to do is:
USE NewDatabaseName
EXEC sp_grantdbaccess @username
I don't need this right?
EXEC sp_addrolemember N'db_owner', @username
In which tables of master database that I can check if I have this @username already have the setup.
Right? Besides what N'db_ownder' exactly mean, db_owner is one of the system members, just don't get what 'N' here is for.
I know I can use enterprise manager to do the same thing, I am just so curious for understanding something behind the scenes.
January 20, 2006 at 1:26 am
The N'string' thing converts an standard ASCII string to a unicode one.
db_owner is a database role that can do absolutely anything within the database. Destroy tables, alter stored procedures, views etc.
Be very sure that you want to give ASPNET these privileges.
Your best bet is to set up your own database role or roles and define precisely what permissions you want that role to have then make your ASPNET user a member of the roles they need to be in.
In my MASTER database I have just SA and guest as users.
Guest is a special user that isn't associated with any SQL Server logins and is necessary so that the system stored procedures can be called in other databases.
I am pretty sure that you should not have ASPNET as a user in the MASTER database.
exec sp_grantlogin only has to be run once on an instance of a server.
In Enterprise Manager if you look at the properties of the login you will see a tab for database access.
The check boxes next to the database do the equivalent of sp_grantdbaccess.
The lower box labelled "Permit in database Role" does the equivalent of sp_addrolemember.
I prefer to stick to scripts because they can be saved and kept under source control in the event of disaster recovery.
January 20, 2006 at 7:21 am
I'm going to piggy-back on what David has already written just to try and give an analogy that may help in visualizing the different levels of access.
Think of SQL Server like this: you have a room full of locked filing cabinets with each filing cabinet representing a database. The room itself is secured. Inside each filing cabinet are smaller boxes which are locked, and these represent the database objects such as tables and views and stored procedures. So we have room > filing cabinet > locked box.
The system stored procedure sp_grantlogin (and sp_addlogin for a SQL Server based login) let you in the room by providing you with the key. It doesn't matter how many filing cabinets in the room you need access to, you only need to be given the key once to get into the room.
The system stored procedure sp_grantdbaccess gives you access to a particular database, or in this analogy, a particular filing cabinet. However, just because you have access to the filing cabinet doesn't mean you have access to all of the boxes contained within.
And that's where sp_addrolemember comes in. Let's say that a particular role has been given a key. This key is "keyed" so it opens up certain boxes (not necessarily all of them). When you use sp_addrolemember, you're basically giving the key to these certain boxes. Some people might need multiple keys. As for db_owner, imagine all of the boxes open to a master key. The db_owner role holds that master key. As David indicates, be very sure you want to give that person (in this case the ASPNET account based on your example) that master key.
K. Brian Kelley
@kbriankelley
January 20, 2006 at 10:13 am
Wow,
What through explanations from both of you. I will think about David's suggestion.
Brian, no wonder you are a columnist. I read some of the explanations in sql server online book about those three store procedures. But I never understand completely, today, I got it.
Thank you, thank you.
Betty
January 20, 2006 at 12:27 pm
Hi,
I tried to create a specific suggested by David. I am not sure if I did the right way.
In enterprise manager, I add a role under the database "aspnetprogram", it is not standard user but I selected application user and then entered password(sysadministrator) and then clicked OK.
In newly created user property panel, I set up the permission on each table(this database)for this user (select, insert, update, delete), is that good enough this user running agaist database?
No system tables will be accessed for this user, is that OK?
That is my first time.
Thank you.
Betty
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply