So I have an application that automatically creates a new database after the database it is currently in reaches a fixed number of rows. I need to be able to automatically assign a user within the installation the db_datareader role for the automatically created database. I'm kind of at a loss here, any help is greatly appreciated.
SQL Server 2008 R2 – Automatically Granting Permissions to Application Created Databases
sql serverwindows
Related Solutions
Please stop using the user instance=true setting. Not only has this feature been deprecated, but what is actually happening here is that Management Studio is firing up a different user instance of SQL Server than the one your application is using, so it is not surprising that you can't see the same thing in both cases.
(Usually, the symptom of this is that someone runs an update from their app, then checks the table in Management Studio, thinking there is a bug but not realizing that the update affected a different copy of the database.)
Create the database on your running SQL Server instance (or attach it there). Then connect to that server the same way, without the User Instance setting, from both SSMS and your app. Lots more information here. Don't create the database every time the application starts - that just sounds horrendously wrong.
All logins exist in the master database. Why it is default is probably related to this, but you can change the default database to your user database if you wanted to. The default being master simply means they have to explicitly set the user database in the connection string for the application.
The default database has nothing to do on why the logon can connect to the instance. This comes from being granted connection privileges to the instance. This is done by default when using SSMS to create the login.
Additional Info
Public role is granted connect permissions and any login created is automatically part of that role.
The only permissions granted for every endpoint is Connect. The only thing they get at the server level is view any database.
You actually cannot remove a login from the public role either, SQL Server does not let you because it is hard coded in there somewhere. You will most security standards for things like HIPPA or DoD (STIGs) want you to simply harden what the public role can do at the server level and within each database. See "INF: SQL Security - Restricting access to public..." for a bit more information on it all.
Creating a login straight from GUI or from just CREATE LOGIN and you will automatically see these permissions at the server level:
Related Question
- Sql-server – Login to SQL Server 2008 R2 remotely
- SQL Server: Database Role for .NET Program to Retrieve MSDB Table and View Rows
- SQL Server – Automatically Set Read Only Permission for New Databases
- Sql-server – How to grant execute permissions to a stored procedure but not to the underlying databases
- Sql-server – How to create a SQL Server user that can restore databases to a database server
- SQL Server – Grant Permissions to All Databases Using Server Role
- Sql-server – Simultaneous SQL full backups of newly created databases
- SQL Server – Automatic Full Backup for New Databases Created with EntityFramework-Migrations
Best Answer
Add the user to the DB_READER role. Like this:
Copied from https://stackoverflow.com/questions/456346/t-sql-to-grant-db-datareader-and-db-datawriter