SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission] FROM [sys].[database_permissions] sec JOIN [sys].[database_principals] prin ON sec.[grantee_principal_id] = prin.[principal_id] WHERE sec.class = 0 ORDER BY [User], [Permission];
but the results are this: 2 columns - User and Permission
User Permission User1 GRANT CONNECT User2 GRANT CONNECT
IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?
Hi, In my web app I've got two databases (the asp profile one and my own custom one). If I take the code and data and use it to create another website on another box, I usually get a database error saying the permissions are wrong. In this case I usually just give everyone full control and it works, but this obviously isn't good practice!So, I'm wondering what permissions on database files does ASP need? Which users need what permissions? Thanks
I have a user that need to create stored procedures but as the dbo account and not his own account so that the stored procedure is called dbo.storedprocedure and not domainuser.storedprocedure. He is a database owner but in order to have this happen I have to have him in the local Server Administrator group. What have I done wrong?
Also, he need to be able to run Enterprise Manager and SQL Ananlysis manager but I do not want him to be a local administrator but they will not start if he is just a local user. How can I accomplish it.
I have db 1 that has a process user called db1process that calls an exec spuserprocess
now spuserprocess has in it a call to dbo.db2.spnewuser
and i gave spnewuser also permissions to userprocess but spnewuser also does selects from other tables and it is returning a permission error -- do i have to give spuserprocess access to all the other tables? is it not enough to give access to that procedure?
We have a system at work that copies (using DTS) over databases fromone SQL Server box to another every night. The copying process dropseach target object and then recreates them. On the 2nd SQL Server boxI have an account setup that is supposed to only have access to runqueries on the databases that get copied over every night. However,because the DTS packages are dropping the objects first we are losingthe table level permissions for this user, so this user can't accessthese databases the next day. Is there a way to automate resettingthese permissions on each table in the databases? Perhaps I should beusing replication as opposed to DTS packages for copying over entiredatabases? If I used replication, would this avoid losing thepermissions that I need?Thanks,Jeff
I recently transfered my web app to my Windows 2003 server and am having difficulty getting the system to allow me to login.I am using MS SQL Express 2005 and the SQL Express Manager.I have a user/password setup with SQL Authentication. It appears that I am connecting okay, but I am unable to login to the site. I have tried three different accounts with no luck. I know I am using the correct passwords.What permissions do I have to grant my database user to allow me to login to my site?As I am new to SQL Express and the manager, a more specific reply would be very helpful.Thank you,Joshua Foulk
I have a db that I use as the backend to an Access application. The application looks at 2 databases that are on the clinet machine. A db from a vendor and my new database. I can read the vendor db (with the proper dns), but I can only read my db when the user has admin privileges. I have granted the public all permissions for the table on my db. What am I missing?
Also, this is a db on a Small business Server 2003 so all of the SQL tools do not appear to be there.
When a new Database is created ( some , not all. All that begins with 'PW' ) i want to add a windows-usergroup to it so they can access the DB. Normally I could do this by adding this user to the model-db but it's not for all DB's that are added (The other DB's must not be accesible for this usergroup ). The DB's are added by an External App so I have no control over it.
So I was thinking, no problem we can just add a trigger to the master..sysdatabases table and add if necessary the permissions. Not -> Even with allowupdates = 1 you cannot add a trigger to a system table ( a bit overprotection from ms, should be allowed if you know what you are doing ).
I would like to avoid running a job every x time to look if a new DB has been added, the DB must be available within minutes after creation.
Our staff login to SQL Server using NT Authentication. The logins have Security Administrator, Disk Administrator and Database Creator Server Roles.
Staff memebers create a new database with Enterprise Manager and are automatically the dbo of that database. They then need to restore the database (again using EM) from a backup file sent to us from various clients. Obviously their login/user will not exist in this external backup file we have been sent. When they restore the database (and they are large so this can take an hour), the restore is almost complete when it gives the error :
Server user 'BLAH' is not a valid user in database 'clientdb'. RESTORE DATABASE is terminating abnormally
The user is no longer dbo or even part of the database they just created.
IS there any way for me to get around this error without making all our staff System Administrors ?
I have written some code that selects all the databases on our server then uses a while loop to execute some dynamic sql to query a table in each database. The problem is some of the databases dont have the correct permissions so the query breaks. Is it possible to check to see if the database has the correct permissions before I execute the dynamic SQL and if it doesn't move onto the next one in the list?
I've moved a database from 2000 to 2005 and in 2005 I cannot see the permissions for this Database Role that I created. In 2000 you just right-clicked, selected properies and clicked on permissions. Am I going mad here or is this not an option in 2005. If not, how do I see the permissions that have been given to the role?
The 2005 database I refer to is on standard edition SP2
I created an application using VB.NET, which performs accessto SQL database. the server is MS SQL 2000.
I got a few questions:
a. The application performs access to tables, performs SELECT transactions, and calls stored procedures. I want to define a userlogin that is only permitted to commit UPDATE and INSERT only by the stored procedures, and not by direct commands. Is that possible ? Do I have to deny access to READWRITE in the tables? If I do deny - will the user be permitted to call stored procedures that performs the INSERTUPDATE those tables ?
b. Do I have to create a LOGIN or a USER for the specified requirements ?
c. Where can I find REALLY detailed information about what I need to create (login, user, role ect.) ? I find only general stuff...
d. The user-defined role I need to create - is it considered as an application role (because it grants an application access to the database) or is it called SERVER ROLE ?
i would like to create a new database role that has exactly the same permissions as an existing database role. the combinations of permissions are complicated enough to make this a time consuming task worthy of a script of some sort. any suggestions?
i'm running SQL Server 2000 and can't find anything about copying roles unless it's through DTS from one DB to another. not sure if this is applicable to basically duplicating and renaming a role in one database.
in case you're wondering why anyone would want to do this you may not be surprised to know that it's a government thing. some policy about differentiating between 2 roles -- even though they currently have exactly the same permissions, the similarities might change in the future.
We have a monitor tool to monitor our SQL servers.The vendor has a script to grant permission to application account to SQL server so that they can do the monitoring.
I don't want to grant system admin role, but the script they have is very specific, the minimum is:
must be a member of db_datareader role on the msdb database must have view server state permissions view any definition connection permission to master database execute permission on the xp_readerrorlog stored procedure connect permission to the msdb database must be member of db_Datareader role in the msdb datab ase connect permission to all databases.
The script given by vendor is as follows in the attachment.I don't like the last execute statement for if a new database is created, we have to remember manually add the monitoring acccount to that database.
I know in SQL 2014 it has a new feature of connect to any database, but unfortunately we are using SQL 2012 and 2008.
script to connect to any database but no need to add that each time we creates a new database.
I have two servers (server 2003) both running SQL Server Enterprise Edition Version 8. One SQL server is Primary, the other is Secondary. The system was doing a daily export from primary and import to secondary, but that quit working a while back. I'm trying to copy the databases from the Primary to the Secondary and than set up replication. I'm already stuck at copying the databases over using the copy database wizard. The servers are not on a domain, and the SQL server service is running as user ./sql. When using the copy database wizard i must use windows authication for it to select the Primary as the sourse, as inputing the SQL Server authication user (sql) and password says it cannot connect. When i select one of the databases to copy and run the wizard it gives me an error code -2147467259, in which google did not help me. It runs through the process of creating a temporary share, puts it in single user mode, detaches the DB, but fails to copy the file. I believe it is some sort of permissions issue on the drive, and gave user sql full access, and after that didnt work i gave "everyone" full access temporary to see if that worked but didnt. Do these servers need to be on a domain for this to work? I also read MSoft knowledgebase http://support.microsoft.com/kb/274463 Id rather not have set these up for a domain. Thanks!
Brief description of the problem:My production server has about 50 databases and various permissions aregranted to public role on all these databases. Because of this any newuser added to any database gets unnecessary access to objects by virtueof being a member of public by default. I would like to fix this flawin the way the server is setup as below:1. Setup a new database role called NewRole on all 50 databases andcopy permissions to NewRole from public.2. Add all existing users to the new Role.3. Remove all permissions from public.Any suggestions on scripting this task are welcome.
Hi all,I need some help to access an SQL db on another machine. I am using VB.NETand remoting to make a client/server connection...although I don't thinkthis is relevant to the question.I have been asked to help with a small db project that will reside on ouroffice server and have approx. 5 users.I have installed a copy of MSDE on my development machine and plan on doingthe same on my office 'server'....in fact it may well already be on there. Ialso have another testbed to act as the client.Because I don't really know what I'm doing...........I have copied the dbcreated on my client to the server. I have done this by 'cheating'. I usethe VB.NET IDE to create the correctly named db under the sql server. I thengo to this file in windows explorer and write over it (and the .LDF) withthe file from the client.When I try to connect to this db remotely I get a fail to connect errorabout not recognising 'localmachinenameguest'...sorry, not the exact errorwording. I can provide this if it will be helpful.Can anyone point me in the right direct?I don't have any sql/db tools other than anything that is included with MSDE(which I don't think is much).I will be posting other Q's for any experts out there!Thanks in advance.Phil
I want to allow administrators of the program to change the permissions of the database roles. Is there a way to retreive the the specific permissions granted an denyed to a user and a database role. For instance, a store procedure that you call, pass it the database role and you get whether SELECT is allowed on table1, or if UPDATE is denied on table2, etc.
I have recently restored a backup of a SQL Server 2000 Database (from my production server €“ which is a shared hosting service) on my dev machine so I can do some testing. The issue I am having is with the permission of objects. Some of the objects were created with the dbo user and some with another login (myLogin). The ones that were created with dbo work fine on my dev machine. The others do not work unless I prefix them with €œmyLogin€?€¦ I get an €œInvalid object name€? if I don€™t prefix them. This is problematic because there are hundreds of stored procs and tables that I would need to modify in order to use them on my dev machine.
Can anyone tell me how to create a new user with the necessary permissions so I can execute my procs and access tables without having to prefix them?
Hope this makes sense €“ please let me know if it doesn€™t.
Is there a way to script out a database role from SQL management studio? I can only get a script for create and drop. I am looking for a script that shows all object permissions that the role has in a database.
I have a larger stored procedure that is running, but I am getting stuck on where I need to grant permissions to a user in a a different database on various functions and stored procedures. For example:
Code Snippet
use [Database1] grant exec on [Database2].[dbo].[MyFunction] to bob
returns this error: Cannot find the user 'bob' , because it does not exist or you do not have permission.
However, I know 'bob' exists, plus when I change the use statement to Database2, the line of SQL works correctly. Given the nature of the overall stored procedure this will be running in, I won't have the ability to just change the use statement. Is it possible to grant permissions to a user on a different database without explicity having the use statement set to a particular database?
I notice when I create a new database within my SQL Server that the permissions for the new database automatically adds a user (Who is configured as sysadmin) with dbo permissions to this database.
Both within the login properties of the select user (User mapping) is listed as Default Schema, dbo and within the permissions of the database listed as user with connect permissions.
I have other users configured as sysadmin and they do not get this rights (They are not expliticly listed within user mapping with dbo or permissions as user within the database).
I've inherited this system and wonder if the user has somehow changed the new database procedure so it changes the default permissions of new databases.
Any way to check what he's done, I can see no differences between him and the other sysadmins but he's definatly specifically listed as a dbo on all new databases.
Although I don't mind him having access, he's a sysadmin after all, I'd like to make it uniform thoughout the system, i.e. using the inhertited permissions rather than specific permissions that seem to be created when the new database is created.
When you create a stored procedure and give the user execute permission, you don't need to give the user select permission on the table used in the stored procedure.
If one of the tables in the stored procedure is a synonym referencing a table in another database, and the user is already in the other database, you get a select permission denied on that table and I could only get it to work if I gave the user select permission on that table.
Is there a way around that, since I hate giving select permissions on tables?
Hello all, this is my second post to this newsgroup. It's a questionabout stored procedures and permissions and how these behave betweendatabases.Here's the scenario. I have a database that stores information for asystem "A", and I have a different database on the same SQL serverthat stores the login and other info "LOGIN". I write a storedprocedure in the "A" database that checks some tables in the "LOGIN"database, let's call this "SP_A".Additionally I have a user account that accesses all appropriatestored procedures in "A" called "USER_A", and the same for the "LOGIN"database, "USER_LOGIN".Here's the part that raised my curiosity. I log into the server viaQuery Analyzer using the "USER_A" account. I run "SP_A" which does ajoin between some table in "A" and another table in "LOGIN". I give"USER_A" execute permission on "SP_A", then I try to run "SP_A" andget an error:SELECT permission denied on object '(table in "LOGIN" database)',database '(real name of "LOGIN")', owner 'dbo'Huh? how come I need to assign additional select permissions in thisdatabase if I'm not doing an actual select statement? I'm not evendynamically running a select statement through an exec function. Thisjust struck me as odd, seeing as how I never explicitly set SELECTpermission on any table in "A" for "USER_A", yet my stored procedureworks, but between databases I have to assign extra permissions for astored procedure "SP_A" access to the tables in "LOGIN".Anyone able to explain this behavior? Because I'm at a loss and I'veonly been doing this DB thing for about 2 years.Thanks in advance, all.-TJ
I want to know how to copy tables and data from one database to antoher database including table permissions. Presently i am using Integrity security services. Is it having any option in Integration services or sqlserver 2005.
We're trying to follow the principle of least privilege here in setting up a user account for our website to use to access SQL Server 2005, but we're having a nightmarish time getting it to work.
The issue seems to be trying to get a limited access user account the ability to cross databases.
Here's the situation:
We have a User [WebUser] that we want to grant access to the database. This account has a login [WebUser] that has username=WebUser and password=ALongPassword.
This user only calls stored procedures in the database [WebData].
However, some of the stored procedures in [WebData] call stored procedures in the database [dbutil].
One of the stored procedures in [dbutil] inserts records into a table in a third database [dbutil_temp].[DebugLog].
This all works out great from my development account using Windows Authentication.
But as you might guess, if I do something like "EXECUTE AS [WebUser]" and run the same procedure on [WebData] things fall apart quickly. I've looked online regarding cross-database ownership chaining, but quite frankly, the whole users/logins/roles/schemas security model is confusing, and I'm getting nowhere fast on my own.
We really only want [WebUser] to have CONNECT and EXECUTE permissions on the primary [WebData] database, but it seems like we've got to do a lot more than that to get this to work.
