SQL Security :: Assign Database Permission To All The Users?
May 31, 2015How can I assign permission to new database for all the existing user in SQL.?
View 3 RepliesHow can I assign permission to new database for all the existing user in SQL.?
View 3 RepliesHi Team,
In SQL Enterprise Manager, when we expand "Database -->Users", we see the
users there. When we expand "Security --> logins" we see the same users there.
Can you differentiate these two.
Thanks
Santhosh
I have installed new SQLServer2012 instance and my domain user have sysadmin privileges on this instance. I have a restore procedure and it will execute WITH EXECUTE AS 'domainmy username', for all the developers have exec permissions to this procedure. But newly installed server this procedure was failing with the following message. But the same procedure executing fine on other servers.
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
What is the correct way to create a security group that allows the group members to Select (Read) the content of a database?
1. Create a security group in AD
2. Add the required members to the group
3. Add the security group as a login on the SQL server (Under Security>Logins)
4. Add the security group to the specific database with Grant in Connect and Select
I am trying to do a schema compare and data compare via VS2012 and I am getting below error: The reverse engineering operation cannot continue because you do not have View Definition permission on the 'Target' database.
Whats interesting is I created a viewdefinition role and added the group(to which the user belongs) to the role. However I dont get the error if I make the group the dbowner. Is this a bug?
Is there any way in which I could distinguish a group of Database users from the other users. Say for e.g. store the Database users hierarchically, etc. Adding a Prefix/Suffix to the user name as a distinguish-er will not work in my case. I want to restrict the deletion of these Database users. Even the login with sysadmin or serveradmin rights should not be able to DROP those Database users.
View 11 Replies View RelatedIs there any maximum cap on the number of users that could be created in a given database of MS-SQL Server?
View 4 Replies View Related
My developers would like a 'sandbox' database with full ddl and dml permissions, however, they do not want others to read/change/drop their objects. With SQL 2005, can DDL permissions be granted to a user at the schema level? I'd rather not set up a database for each developer.
we have an application which lets users connect to production database with windows credentials, They are able access the sql tables too with windows login. I want to restrict them from accessing the sql tables. How do I do that.?I tried a db_deny but that prevented them from accessing the application too.
View 10 Replies View RelatedI am looking to write a script that would give the list of all active users in a database with their privileges.
Sample would look something like this.
Login User Schema IsdbOwner canWrite canRead
xyz xyz dbo N Y Y
I'm developing a vb 2005 application and I€™m creating the users directly to the database. I want to assign them names.
I want to do something like this:
CREATE TABLE admin.db_users (
id INT CONSTRAINT db_user_pk PRIMARY KEY,
[name] VARCHAR(50) CONSTRAINT db_user_name_nn NOT NULL,
authentication VARCHAR(25) CONSTRAINT db_user_authentication_nn NOT NULL,
CONSTRAINT db_user_fk FOREIGN KEY(id)
REFERENCES sys.database_principals (principal_id)
ON UPDATE CASCADE
ON DELETE CASCADE
);
GO
This is the error that i'm getting:
Msg 1767, Level 16, State 0, Line 1
Foreign key 'db_user_fk' references invalid table 'sys.database_principals'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
How do I solve this problem or how can I do something similar.
I am trying to revert back to Windows 7 after upgrading to Windows 10, however it will not let me and the following message occurs: "Remove new accounts.Before you can go back to a previous version of Windows, you'll need to remove any user accounts you added after the most recent upgrade. The accounts need to be completely removed, including their profiles.You created one account (NT SERVICEMSSQLSERVER) Go to Settings> Accounts> Other users to remove these accounts and then try again".However I did not create any new users and there are no other users listed in the Accounts section.
View 2 Replies View RelatedJust wondering if anyone knows of a useful command to assign execute permissions to a batch of stored procs to a user/role. I've got too many stored procs to manually go thru the steps of browsing for them and scrolling thru each one and clicking "execute" for each one.
Also, would like to know if its possible to update a batch of stored procs that begin with a prefix like "spSomething_".
Any info would be helpful! TIA.
I want to make data changes in read_only database , that's why i must set database read_write. While database is at read_write mode, i want to be sure that no one makes change in database.
For this aim, i write the code below, but i suspect that after setting the database read_write, till the setting database
single_user ,is it possible get DML script from another user. Is the code below enough for this operation. Or is there another way?
Reminding: Read_only database can not be set single_user mode. That's why, first you must set database read_write.
The code;
use master
alter database xxx set read_write
with rollback immediate
alter database xxx set single_user
with rollback immediate
use xxx
update tablexxx set columnxxx=yyy
use master
alter database xxx set read_only
with rollback immediate
alter database xxx set multi_user
with rollback immediate
i have created a new login in primary server and provided dbowner permission to primary db.how do i transfer this login to secondary server and assign the same permission to secondary db ?
View 2 Replies View RelatedHi guys,
We have a scenario where there are about 50 tables in our database and we want to build an intranet web application for users to with the office to access those tables.
Users ability to access tables falls into diferent category:
Some users can NOT view some tables at all
Some users can ONLY view some tables but not insert/update any field
Some users can view and also insert/update some tables (in the same time they might not have view(select) permision on some other tables)
Now, what is the right way to implement this.
I say we have to have a Role, RolePermission, User, UserPermission inside our database to implement this (something which would look like the Roles and Users inside MSSQL) and we only have one user for our Database (MachineName/ASPUSER) to access the database and all the tables within
My colleague says NO, instead of creating all these tables and implement this, we add every user of our application as a Database user inside MSSQL in the Databse Users.
All the web application I have seen so far, DNN, CommunityServer, ... the have tables to implement all these and they don't add users inside the MSSQL.
Now which way is the way to go with, and what problem might we fall into if we use SQL users, is this possible at all. How can I convince him that we have to make and use our own tables to manage this.
Thanks for any help,Mehdi
Permission problem w/ users running some queries
I have a SQL-authenticated user that I want to be able to run a pass-thru query. The user was created solely for this particular job. I've given it appriopriate permissions on all the tables, views, and stored procedures directly involved in the task. BUT, the task fails.
If I have the query perform a simple task, like SELECT * FROM TABLE, it works,
but, not on this:
CREATE PROCEDURE sp_gtn_RunFromAccess AS
EXEC msdb..sp_start_job @job_name='CA_GTN Download From Mainframe'
GO
If I give the user the DB_OWNER role, it fails; however, if I make it a sys admin, it suceeds. That is obviously no good. It also suceeds it I put sa and a password in the ODBC string, but that is unacceptable, as well.
What kinds of permission am I overlooking?
Any thoughts on this?
Thanks,
Michael G
How to find the list of logins/users who do not have any permission (except default Public) access.
View 4 Replies View RelatedThere's something I can't quite figure out about user creating
The application that I'm currently working on is interacting with DB, therefore every time you use application you need to login as user which is fine. The problem is that certain users should be able to create new users and the new user may even have the same level of permissions as the one that's creating it ( like admin creating another admin acount or some like that).
Question is how can I allow users to create these user with giving them as few permissions as possible.
If there's is somewhere a code sample on the net I would appreciate the link.
We have a third party application and wish to create a report based upon a view.
The strange thing is logged in to the server as a SQL SYSAdmin account, we cannot view the data via the view. SQL Admin accounts are setup correctly and there is nothing different on this particular server. No errors are returned just a blank view with no records.
Could this be a permissions problem or orphaned schemas in that particular database? I thought SYSAdmin could view and do just about anything and the people who use this particular database would not have the know how on denying permission to the SYSAdmin role.
Thanks
N
Hi Every body,
I have newly joined this group. I am new to DB administration.
I wanted some information as to if my Server crashes (which has) & i reinstall SQL server, will restoring master database restore all my permissions & security which was set before crash. It would be great in anybody can help me on this.
Regards,
Krishna
I have windows 2003 reporting services 2005 that has been working fine. Now all of a sudden when I go to http://localhost/reports I am unable to assign security to folders(the link is hidden) the New data source button is hidden, the new folder button is hidden, upload folder and report builder are also hidden. I am logged in as aministrator.
Also when I connect to reporting services through SQL Mgmt Studio I have the same issue, there are no options for permissions only a general option.
I'm sure it's a permissions issue but what do i do to resolve?
Thx
best possible way to provide truncate table permission to a SQL Server Database user (ddl_admin role cannot be granted to the user in my case)
View 3 Replies View RelatedI am very early on in developing a website to track issues with projects which is tied to a SQL database. I have my Projects Table, my Users Table, and am creating a third table to track issues. I'm wondering what is the best way to assign specific users to specific data/projects. The user should only be able to view & update the projects assigned to him. He should not be able to see other projects. What is the best way to assign projects/data to the users to make sure they are only viewing their data?
View 1 Replies View RelatedI have had this issue just pop up. I have local users who can connect fine, but my users that require connection by VPN cannot connect. I get the server not available or access denied error. I did confirm that the VPN'ers are connected to the network correctly and can see that their shares and mappings are correct. Any ideas? Thanking you all in advance!!
View 6 Replies View RelatedI want to grant CREATE, ALTER, VIEW permission to user but not DML permission?
So user can create Object but can't drop and delete it or user cant insert , update,delete from table.
I have Given db_datareader,db_denydatawriter, and granted create, select, alter permission.
But user is able to drop.
I have to give three users read access to a database.  My manager told me to map them to a User called "zxDatabase.Read.ug". I scripted the User and got this...
CREATE
USER[zxDatabase.Read.ug]
FORLOGIN[zxDatabase.Read.ug]
I don't see a Login at the server level named zxDatabase.Read.ug, so this is confusing. Where is this login in SSMS?
How do I map my three users to user zxDatabase.Read.ug. Or am I not understanding correctly? Do I just add there windows login to the database as Users and configure them the same as zxDatabase.Read.ug
We wanted to know list of  users who had logged into the SQL Server from last 10 to 30 days.
View 2 Replies View RelatedI have developed a site on my local machine utilizing the built-in security/roles provides by ASP.NET 2.0. I am trying to publish the web application to a production server. It will publish correctly, however the ASPNETDB is not recognized. The folder app_data is created with the proper database files. My question is how do I set this up to use the data I have created in this database? I've searched on how to accomplish this but haven't found any definitive answers. I have found ScottGu's blog to be useful, but I was wondering if there is a different way.
1 - Do I push the data to a SQL Server install via SQL scripts?
2 - Can I link to the ASPNETDB that is created locally on my machine?
3 - If I make changes to the application and re-publish, is there a way to keep from overwriting the ASPNETDB on the server?
I am a unsure of how the built-in roles/security works when you are ready to push something to production. If someone wouldn't mind pointing me in the right direction, it would greatly be appreciated. Any literature, advice, links, etc.. would be great.
- Thanks
Andrew
I have a linked server set up and working correctly. I can create a query to get all the users from active directory with something like this:
SELECT [name], [samaccountname] from OpenQuery( ADSI,
'SELECT name, samaccountname FROM ''LDAP://DC=domain,DC=com'' WHERE objectClass = ''user'' and objectCategory=''Person''')
Now I am trying to select all the users in a specifed security group, but I am not having much luck. What is the best way to get this?
Thanks much.
Hi,
The Report Manager portal has many folders. For each folder there are specific users with different roles.
I am trying to figure out the way to extract User, folder wise security data. I want to run a query and retrieve users name, the folders they have access to and the user role corresponding to that folder.
Use ReportServer
SELECT u.UserName, r.RoleName FROM users u, policyuserrole pur, roles r
WHERE pur.UserID=u.UserID AND pur.RoleID=r.RoleID
The above query fetches all the users and their roles.
The folder information corresponds to Path column of Catalog table. Am unable link this table with the above query.
TIA
Is SQL Server sensitive to Domain group name? Like "Domain Admin"?
I have user that belong to "myDomainDomain Admin" group. Group is in SQL as sysadmin but user cannot login using domain credentials. When I move that user to a different domain group which that group is in SQL again as sysadmin my user is able to login.Â
Environment: SQL 2008 Standard Edition.Â
I am able to deploy and run reports from my screen at work, but some non-developers cannot run them. They can see the reports, but get the following error message when they click on the report:
An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'. (rsErrorOpeningConnection)
For more information about this error navigate to the report server on the local server machine, or enable remote errors
--------------------------
-----
- I assume the preceding error has something to do with credentials. I am using Windows security authentication for this. One user has worked through this and is now getting cannot run report query (one of the report queries in the report). Help would be appreciated.